Select/Where Exists Issues- Any Ideas?
Feb 27, 2007
I am trying to use the following statement/query to delete any rows in tblcntslist table where tblcntslist.Company2 = tblCompany.Name.
Basically I am trying to look in the tblCompany table, look at the 'name' column, see if there are any entries in there that match entries in the tblcntslist.Company2 column. If there are matches, I want to delete them from the tblcntslist table.
I already "massaged" both table columns and removed all special characters, spaces, and other meaningless info that could result in incorrect matching. This way it is a match based on pure letter/number strings. I even set all as UCase in both table columns.
Code:
Delete from tblcntslist
where exists
(select * from tblCompany INNER JOIN tblCompany
ON tblCompany.Name = tblcntslist.Company2
where tblCompany.Name = tblcntslist.Company2)
I am getting the following error:
Server: Msg 1013, Level 15, State 1, Line 4
Tables or functions 'tblCompany' and 'tblCompany' have the same exposed names. Use correlation names to distinguish them.
I am a bit confused as to why. Any ideas?
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
I have a table that has the following fields:tblECHECKID (autonumber identity column)PTIDBatchnumPageDataPointDPValueThere are many other tables on which I run a script that tests to makesure the data meets certain entry requirements (I don't want to putrestrictions on the forms because the data entry personel have toenter what is on the survey).Anything that fails is copied to the tblECHECK table. All the datacopied into the table are identified by a batchnumer (batchnum).I want to write a script that will look at all the records in thetblECHECK table, and delete any duplicates (the batch numbers are theonly fields that would be different).I have tried the following with no success (it returns 0 records):Select *From tblECHECKWhere batchnum ='batch8' and not exists (select * from tblECHECK wherebatchnum = 'batch7')Any help would be appreciated.
View 1 Replies
View Related
Mar 12, 2008
IF( NOT( EXISTS( SELECT 1 FROM updatetable WHERE ScriptName = 'MY_Scripts' )))
RAISERROR('script_dateupdates is required prior to running this script.', 30,1);
This is the Exists statment on the top. I was trying to find out what does it read. Can someone reat that for me.
Also why Not is before the brackets why it is not like If NOT EXISTS (Select....... ) acutally this is my concern to find out.
Thanks,
View 7 Replies
View Related
Jun 11, 2008
I have a Select statement that was working just fine:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
I am completely lost here.
Thanks for any help
View 3 Replies
View Related
Jul 20, 2005
I tried this:IF Exists (Select 1 from TESTA..POP10100 where PONumber=@PONumber) and(Select 1 from TESTA..POP30300 where POPType<>2 andPOPRCTNM=@ReceiptNo)BEGINENDIt says Incorrect Syntax error? I tried to cover the whole thing withan extra parantheses. It wont work eitherWhat am i doing wrongThanks, Girish
View 2 Replies
View Related
Jun 13, 2000
I would like to know how can i check from SQL statement that
whether LinkedSERVER exists or not like we search a table from "sysobjects"
Is there a way where i can search my LinkedServer exists ?
if exists (select * from sysobjects where name = ['mylinkedserver'])
View 3 Replies
View Related
Feb 18, 2010
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
View 15 Replies
View Related
Jan 6, 2004
Im trying to select rows on the following criteria
My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.
I am able to return the following:
Comment UnReadMy Job 164
Comment UnReadFor Cam 166
Comment ReadThe Job 157
Comment ReadThird 159
Comment ReadMy Job 164
On Site Visit ReadMy newest job for log test 167
but as you can see jobid 164 appears in both groups and I need it to be one or the other
I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:
CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
@UserID INT
AS
BEGIN
IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead')
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead'
ORDER BY BackUp_Read ASC
END
ELSE
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'Read'
ORDER BY BackUp_Read ASC
END
END
GO
Thanks again
View 12 Replies
View Related
Jul 31, 2015
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
View 3 Replies
View Related
Nov 7, 2007
Hi,
When i try to save my stored procedure.. i am getting the above error and this is my sproc
1 INSERT INTO Statement..ClientSources
2 (
3 ClientId,
4 ClientSourceId,
5 SourceName
6 )
7 Select Distinct
8 @ClientId,
9 SOURCE_NUM,
10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
11 WHEN 2 Then SRC2NAME
12 WHEN 3 THEN SRC3NAME
13 WHEN 4 THEN SRC4NAME
14 WHEN 5 THEN SRC5NAME
15 WHEN 6 THEN SRC6NAME
16 WHEN 7 THEN SRC7NAME
17 WHEN 8 THEN SRC8NAME
18 WHEN 9 THEN SRC9NAME
19 WHEN 10 THEN SRC10NAME
20 WHEN 11 THEN SRC11NAME
21 WHEN 12 THEN SRC12NAME
22 WHEN 13 THEN SRC13NAME
23 WHEN 14 THEN SRC14NAME
24 WHEN 15 THEN SRC15NAME
25 END
26 FROM
27 PlanDBF p
28 Where
29 p.PLAN_NUM = s.PLAN_NUM
30 ) as SourceName
31 FROM
32 SourceDBF s
33 Where
34 SOURCE_NUM NOT IN (
35 SELECT DISTINCT
36 ClientSourceId
37 --SourceName
38 FROM
39 Statement..ClientSources
40 Where
41 ClientId = @ClientId
42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help will be appreciated.
Regards
Karen
View 4 Replies
View Related
Apr 28, 2015
I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
==============================================
Declare @mSql2 Nvarchar(max)
SET @mSql2= (select * from Tablename)
================================================
View 2 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store   Type   Sales
11 Â Â Â Â Â A Â Â Â Â 1000
23 Â Â Â A Â Â Â 1980
23 Â Â Â B Â Â Â 50
5 Â Â Â Â B Â Â Â 560
I want to filter the store in "where clause" Â where
1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= Â (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 Replies
View Related
Jan 8, 2008
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
View 2 Replies
View Related
Jul 27, 2015
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
View 9 Replies
View Related
Oct 29, 2004
I need for a database to give my users and indication that a renewal has been complete. Basically what happens is every year once a month a report is generated from sql of how many employees need their gaming license renewed the filter is based off of a field called final suit. I need to find a way to let them know through the database that an employee has been renewed. anyone got any ideas??
View 9 Replies
View Related
Apr 9, 2007
Hi all
I am fishing for ideas on the following scenario and hope someone can point me in the best direction.
I create a new table and insert data from relevant other tables but i want to set the data in the new table colums to set widths that have leading zeros where applicable.
I.E the new table column is varchar (10) the data going in to the column is coming from another table where the size was varchar (8) but the data was only 2 characters in size so i want to pad it out to the full new varchar (10) with leading zeros if that makes sence.
I am really just trying to get some ideas on the best possible way to do this thanks.
View 2 Replies
View Related
Nov 9, 2007
I have a table with 18,000 records with beg_eff_date since the year 2005. I need to separate the entries based on their daily activity. For example if Beg_eff_date 01/0/2005 then day is "1" if Beg_eff_date is 01/27/2005 then the day is "27".
Repeating the same process until I reach "NOW" present time. Any ideas of how can I do this?
Thank You for all your previous help and the current one!!
View 9 Replies
View Related
Nov 12, 2007
Hi:
I created a Temp Table that hold a collection of records by date.
I need to do create calculation that give me the Total fuel used per day:
c.rcpt_nom_vol -c.rcpt_fuel- c.rcpt_act_vol = Total Fuel used per day.
Then pull the result of this calculation and assign the result to the specific day of the month. For example is the Calculation Result is 4.25 on Feb 12, 2007. Then the record is insert into a Temp Table as 4.25 day #12.
Does anyone has an idea of how to do this? Thanks and Let me know!!!!
Create Table #TP_Daily_Imb(
contract_nbr char (8),
contract_sub_type char (3),
contract_type_code char (3),
current_expirtn_date datetime,
nom_id char(12),
nom_rev_nbr char(2),
beg_eff_date datetime,
rcpt_dlvry_ind float,
rcpt_nom_vol float,
rcpt_fuel float,
rcpt_act_vol float,
end_eff_date_DGC datetime)
SELECT Distinct a.contract_nbr, a.contract_sub_type, a.contract_type_code,a.current_expirtn_date,
b.nom_id, b.nom_rev_nbr,
c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel,c.rcpt_act_vol
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')
View 9 Replies
View Related
Feb 5, 2007
Hi everyone
We've got currenlty around 500 dts 2000 in production.
In order to know in what ETL processes we have Oracle connections, or FTP tasks or whatever, we did a VB6 app using dtspkg.dll which load all the properties for each DTS into Sql server tables. So that, then you could see from a specific DTS how many connections, Sql Tasks it had and so on..
How to accomplish the same with SSIS? I know, doing the same but using .Net, of course, but is there any else approximation? I am little bit concerned when we will have hundreds of them up.
Maybe 2005 is offering this feature automatically, I don't know.
Thanks in advance for your time/advices/ideas,
View 1 Replies
View Related
Mar 1, 2008
Am working on an SSIS project and I am not sure how to handle this situation. I have four tables in two completely separate networks {AB} {CD} and I want to populate one of the tables D based on the information from tables A and C with the following scenario I get data from from table A make a lookup transformation to check what did has changed if the data has changed or if there is a new entry in table A get the identity key from table C {they both share a common field} and add the identity key as part of my data inserted in table D
A ----lookup <
data that has changed --- get the identity key from C ---- insert data from table A + identity key into D
View 5 Replies
View Related
Mar 19, 2007
I have a text file that I am importing in a data flow task. Not all of the rows are the same, there are "header" rows that contain a company code.
What I want to do is keep that company code and append that to the row of text that I am writing to a CSV file.
Since you cannot change variables until the post execute, what are my options?
Hope that's clear
Thanks!
BobP
View 3 Replies
View Related
May 16, 2008
I have some performance issues which I can not explain; The database it is running on on a dedicated SQL server box with 8 cpu€™s 4 GB memory. Users are connecting with a fat client.
There is hardly on users using it but they are getting long delays. So I kicked of a profiler trace and sp_blocker. I noticed some queries taking over 30 sec to complete in profiler but when I run them in query analyzer they run in under a second.
I check the output from the blocker and there was no blocking over that time period and it was the only processes actually running.
How can it be so slow I have run out of ideas please any ideas would be welcome
Many thanks
View 7 Replies
View Related
Mar 9, 2007
I'm looking for Ideas on how to handle a Pldege Reminder process. For example; a pledge is made to pay $2400 over the next two years. They will pay $100 per month and each month a reminder will be sent. No real mistery if there is a balance you send a reminder. My problem is how to handle things like what if they want to pay quarterly or annually and how to determine if a payment is really due based on when they paid last, etc... You most likely see what I mean.
If anyone has done this in the past and/or has any ideas and is willing to share I would greatly appreciate any help.
Some stuff that may help you help me better:
tblClient (ClientID)
tblPledge (PledgeID, ClientID, PledegedAmt, PledgeDate,Frequency,NumberYears)
tblPledgePayments (PmtID, PledgeID,PmtAmt,PmtDate)
View 3 Replies
View Related
Jun 2, 2006
I'm new to asp.net. Please help me with some ideas about the design of databases and interface of the web site.
We are a public school district, I want to create a website for families to go on line to update their family and student's demographic information. Basically I put the students' infomation pulled from our student information system software on the web, the parents can log in on line to update it at the beginning of school year. After that, I will create some report and let secretary manually do the update to our student information system.
The demographic infor includes 3 parts,
1. family street address, city, state, zip2 guardian1 primary phones,second phone, emails. primary phones,second phone3, student date birth, gender. may have multiple students in one family
But how can I track which field parents changed, shall I do it in programming in the web form, or later when I create some kind of reports. For I only want to pull students with the fields that updated and give them to secretary to update manully, and I don't want to generate a full report and let them compare which field is changed and then update, it will take them too much time.
Thanks much in advance
View 3 Replies
View Related
Apr 11, 2002
I've read thru all the threads here which say basically the same thing, use a drop table & then a create table to clear the spreadsheet. My problem is the process works the first time to create the table in a new Excel file but fails to clear excel after that. The processes drop, create, & pump data all work without fail but the spreadsheet data is appended to. I've used the wizard as well, saved the package and ran it again without success. Anyone ran into this one?
View 1 Replies
View Related
Nov 28, 2000
I have tried to make my basic audit log do more, but i haven't gotten very far;
In my basic audit log, i record this information:
table
type of change
field modified
old value
new value
db user
date/time
This audit records everything, which is great, but it cannot relate information when i go back to analyze the changes; for example, when a "directory" record is added, a user's information may be entered into several different tables, such as:
name (different table)
addresses (different table)
phone numbers (different table)
If one wanted to look up the changes to addresses of a person in the directory based on the person's name, i could not do it with my existing audit log because the addresses would be in a different table than the name table and there is no relating data in the audit log to relate the address changes to a persons name;
What might be a solution? I have tried a few approaches and am at a loss;
Thank you!
--llyal
View 4 Replies
View Related
Jan 18, 2006
it is working but takes about 3-4 seconds per exec.
CREATE PROCEDURE isp_ap_calc_apt_totals
@p_comp char(2),
@p_vend char(6),
@p_asofdatechar(8)
as
if (@p_asofdate <= '00000000')
begin
set @p_asofdate = '99999999'
end
delete from XAPAPTTOT
where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @p_comp) and (a.apph_vend = @p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @p_comp and b.apt_vend = @p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @p_asofdate) and a.apph_unpost_dt > @p_asofdate and b.apt_comp = @p_comp and b.apt_vend = @p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
GO
View 4 Replies
View Related
Jan 12, 2007
To Whom It Concerns - MS Product Design - SQL Server Reporting Services:
I've been working with an ASP.NET web based project utilizing SQL Server Reporting Services for a while and have some things that I think need to be addressed in the next version:
1) I finally got security working utilizing the form based authentication of my Web based app and passing that to SSRS through the ReportViewer control. Not that it was easy. From what I see, a lot of people are having problems with this and so I didn't feel like I was alone. That said, instructions on how to do it need to be easier. Ultimately, it would be better if on the ReportViewer control, you could add properties as follows:
* Impersonate SSRS User
* Impersonization Password
2) Maybe I'm missing something, but in the web app the system logs in to different production databases based on the user id. The only way that I can figure out how to make the same reports available to each database (all on the same SQL Server DB) is to copy the reports and change the data source in the copied directory to copy to the new DB in SQL Server. Could ReportViewer contain a property that allows you to override the connection sring?
3) You have the ability to create PDF's. This is a very nice feature. However, I would like to extend the capabilities that you have. I want to be able to embed a PDF inside the report and have it generated in-line with the report. As an example, I have meta data concerning the PDF kept in the database. When the report prints, I want the meta data printed and the full PDF referenced by the meta data to print. I actually posted a message concerning this to find that there is no solution - I've seen that many people have looked at the message indicating that they are interested in the same feature.
4) It would be nice to have additional controls available - panel, calendar , checkbox, checkboxlist, radiobutton, radiobuttonlist and hyperlink in particular.
5) Dashboard widgets that we can use. Your promoting SSRS as a way to build data decision front ends. It would be nice if out of the box SSRS included several widgets that we could use as indicators to metrics being tracked by the system. As it stands, I can do simple bar, line and pie charts and that is about it. No progress bar, no guages. It would be nice to have some sexier graphing components.
All in all, I do like the product. It was a pain to get setup due to the authentication issues, but I do like it.
David L. Collison
Any day above ground is a good day!
View 3 Replies
View Related
Jan 24, 2008
Hi,
I want to write a sproc whose main table is Order and it inner joins another table called OrderShipTo with a one to many relationship so the way i have written the first query is as follows
SELECT
o.OrderId,
o.OrderDate,
o.PlanId,
o.CreatedByUserId,
o.Quantity,
o.BillToCompany,
o.BillToContact,
o.BillToAddress,
o.BillToCity,
o.BillToState,
o.BillToZip,
o.BillToEmail,
o.BillToPhone,
o.ShipToIsBillTo,
o.BookTypeId,
o.RequiredDeliveryDate,
o.SpecialInstructions,
o.ApprovedBy,
o.ApprovedByPhone,
o.ApprovedByEmail,
os.ShipToId,
os.ShipTypeId,
os.ShipToCompany,
os.ShipToContact,
os.ShipToAddress,
os.ShipToAddress1,
os.ShipToCity,
os.ShipToState,
os.ShipToZip,
os.ShipToEmail,
os.ShipToPhone,
os.Quantity
FROM
[Order] o
Inner Join OrderShipTo os on o.OrderId = os.OrderId
WHERE
o.OrderId = @OrderId
So suppose if i give an OrderId of 15 In the OrderShipTo table they may be 3 Or 2 or none entries for this order in the Ordership to Table So if there is 3 entries in the OrderShip i will get 3 rows of data which is correct... But i just want o.Columns to appear once and i want n rows for the os.Columns to appear.
So in Order To acheive i wrote this query DECLARE @OrderId int
SET @OrderId = 311
SELECT
o.OrderId,
o.OrderDate,
o.PlanId,
o.CreatedByUserId,
o.Quantity,
o.BillToCompany,
o.BillToContact,
o.BillToAddress,
o.BillToCity,
o.BillToState,
o.BillToZip,
o.BillToEmail,
o.BillToPhone,
o.ShipToIsBillTo,
o.BookTypeId,
o.RequiredDeliveryDate,
o.SpecialInstructions,
o.ApprovedBy,
o.ApprovedByPhone,
o.ApprovedByEmail
FROM
[Order] o
WHERE
o.OrderId = @OrderId
EXEC usp_OrderShipToLoadByOrderId @OrderId
But at the end i want all the rows o.Columns + the rows from os.columns... I also tried giving a union of both the tables but that didnt work of type mismatch..
How can i acheive that???
Regards
Karen
View 5 Replies
View Related
Apr 11, 2001
Hi all,
Up until recently one of our SQL 7 databases has been running quite happily. However over the last 2 weeks the users have started to complain about performance levles, operations that would normally take 2-3 seconds now run for about 40 seconds. I have rebuilt all indexes and even ran update statistics althought both the 'Auto create Statistics' and 'Auto update statistics' are turned on. This failed to help so I ended up stopping and starting SQL server but still no luck.
I have run a DBCC SHOWCONTIG and the fragmentation is well within acceptable levels, scan desnity is at 94%. The only other thing I can think of is to reboot the NT server as it has been up for 67 days now. Can anyone else think of anything I might have overlooked?
All replies welcome.
Thanks.
View 2 Replies
View Related
Feb 7, 2000
I have about 20 remote databases that I need a query/scheduled task to run that generates a .rpt file. That part I can get.
However, I need to get this file to another company (via email or ftp, etc) and make it automated.
For example:
Scheduled task runs twice a month and generates a file that then needs to be emailed to an individual or ftp'd to a ftpsite. Is there a way to do this in SQL 7.0 (or 6.5)?
Thanks for all your help,
Laura
View 2 Replies
View Related
Mar 25, 2004
Here is what I have,
select id, name from rss_user
gives me this
r604738 one
r604738 two
r604738 three
r604739 one
r604739 two
r604739 three
r604739 four
I would like to be able to pipe this into a @temp table so it looks like this,
r604738 one,two,three
r604739 one,two,three,four
Any ideas, so far I am drawing a blank.
View 1 Replies
View Related
Mar 18, 2008
SELECT DISTINCT WO_NO, wo_type, wo_status (incomp,compl,open), wo_status_code, wo_create_date, wo_post_date
I need to do a stored proc in sql server 2005 where I have to count the total work orders based on their wo_type + wo_status, i.e. open, compltd, incomplete. For example I may have (wo_types) AB, AC, AD, orders that are in open status. I need to count the total AB_Type, AC_Type, AD_Type then the total of all 3 in another column (as Total Open). I must do this for all wo_status.
I also have to allow the user to be able to enter the date they want. I.e. if they would want to know on 3/17/08 how many orders were opened that day only. I know I have to use a parameter for this but how do i do that?
Should I do separate select statments for each status, multiple tables ?
Also for 'open' wo_status the wo_status_code can change from O to S but it is still considered 'open'. For this wo_status how can I get the most recent status_code for the open order, i.e., I want to count a particular 'open'work order's - most recent status_code in the work order count for a given wo_type -->> the order should only be counted once on any given date entered. So if the status changed from O then S the same at 1pm then 2pm, respectively, then only the 2pm status should be counted. Thanks in advance for your assistance.
View 2 Replies
View Related
