I'm new to SQL. I don't understand yet how to add if statements and other scripting techniques. But this is what I am trying to do...
I have a list of IDs (1, 2, 3, 4, 5, 6, 7, 8). I want to query a table and then display which of those IDs are NOT in that table. It seems like it should be easy, but I don't know how to do it.
Any help would be much appreciated.
thanks
scott
I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".
Employees(ID,FirstName,LastName)
Biotechnology(ID,EmployeeID)
**assuming that EmployeeID in "Biotechnology" is the ID from "Employees" table
Example: We add Employees.ID(23, 33) to Biotechnology.EmployeeID
would be
Biotechnology:
[ID] [EmployeeID]
01 - 23
02 - 33
Hi AllWe are running SQL Server & Outlook with an exchange server. We arelooking for a way to import the address book & email details into adatabase table.We have managed to do this with MS Access by using the import optionexchange(). Is there a similar way this can be done in SQLServer 2000
View 2 Replies View RelatedI want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...
Loop from 0 to ListLength
begin
INSERT Transaction_Data
end
Thanks from an SQLS7 newbie,
jE
Does anyone know which SP to use to get the list of available servers?
I want to call it from a VB app via ADO to provide the user with a choice of servers available.
I am glad to announce that there is now a version of my article "Arrays andLists in SQL Server" for SQL 2005 available on my web site.THe URL for the article ishttp://www.sommarskog.se/arrays-in-sql-2005.html. If you are curious aboutthe performance numbers they are in an appendix athttp://www.sommarskog.se/arrays-in-sql-perftest.html.The old version of the aritlce remains, as the new article covers SQL 2005only. The old version is now athttp://www.sommarskog.se/arrays-in-sql-2000.html.The old URL, http://www.sommarskog.se/arrays-in-sql.html leads to a pagethat links to both articles.And the reason that there are two articles is simply that SQL 2005 addsso many new features: nvarchar(MAX), the CLR, the xml data type, CTE thatall can be used in the realm of arrays and lists.--Erland Sommarskog, SQL Server MVP, Join Bytes!Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/pr...oads/books.mspxBooks Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodin...ions/books.mspx
View 1 Replies View RelatedIs there a way to use PreparedStatements (or bind variables) with SQLstatements that have a variable number of arguments. For example, Ihave an array of IDs for employees of a certain type and I want tohave a PreparedStatement retrieve all of them in a single SQL call.SELECT FROM employees WHERE employee_type = ? employee_id in(?,?,?,...,?)It seems at least in Java that PreparedStatements can only take afixed number of arguments?PreparedStatement pstmt = con.prepareStatement("SELECT FROMemployees WHERE employee_type = ? AND employee_id = ?");pstmt.setLong(1, employeeType);pstmt.setInt(2, employeeID);Is there a way to have a PreparedStatement for a SQL "IN" clause orsimilar variable argument length clauses? I heard that Temp tables isone option where you first insert your array of IDs into a Temp tableand then use a JOIN. But that seems to defeat the purpose as itrequires multiple queries. Is there anyway to something like this:int[] employeeIDArray = getEmployees();PreparedStatement pstmt = con.prepareStatement("SELECT FROMemployees WHERE employee_type = ? AND employee_id IN (?)");pstmt.setLong(1, employeeType);pstmt.setIntArray(2, employeeIDArray); <--- How can I do this?Thanks,- Robert
View 7 Replies View RelatedHi,
I'm trying to get data from WSS lists by web services. I think there are two ways:
- using SSIS standard components (Web Service Task) and
- creating a custom source adapter.
My problem with the Web Service Task is, that the WSS method GetListItems has some non-standard parameters (see part from wsdl-file below). Thus, after selecting the method in the editor an clicking on "Ok", I get the error message "The selected Web method contains unsupported arguments". I think the problem is the missing type-attribute.
<s:element name="GetListItems">
<s:complexType>
<s:sequence>
<s:element minOccurs="0" maxOccurs="1" name="listName" type="s:string" />
<s:element minOccurs="0" maxOccurs="1" name="viewName" type="s:string" />
<s:element minOccurs="0" maxOccurs="1" name="query">
<s:complexType mixed="true">
<s:sequence>
<s:any />
</s:sequence>
</s:complexType>
</s:element>
...
My question: Is there a way to extend the Task to handle such parameters?
Hi,
is there any example how to extract values from a SharePoint list? I searched in Google but I only found something about that someone failed using the web service task for it... If possible I would like to get around any custom .net code (Scripts would be OK), but if I can't get around that, I have to follow that path...
Thanks,
I am desperate to retrieve lists from SharePoint using Reporting Services to consolidate with other data
I got stopped before I ever started. Defined a data source using the following syntax....but then I search for vti_bin or lista.asmx if find none.
http://<server>/<path>/_vti_bin/lists.asmx.
sharepoint/SQL 2005 running in intergated mode on single machine for proof of concept.
after formulating the dataset query I receive an error that
the XmlDP query is invalid.(Microsoft.ReportingServices.DataExtensions)
Any ideas how I screwed things up?
.
Hey I'm using this DAL to populate a generic list called assetDetailListHowever I'm adding a 2nd result set to my stored proc, protected void GenerateAssetDetails<T>(DbDataReader dataReader, ref List<AssetDetails> assetDetailList) { while (dataReader.Read()) { // Create a new culture AssetDetails assetDetail = new AssetDetails(); // Set the culture properties assetDetail.FileName = dataReader.GetString(dataReader.GetOrdinal("filename")); assetDetail.Title = dataReader.GetString(dataReader.GetOrdinal("title")); assetDetail.AltTag = dataReader.GetString(dataReader.GetOrdinal("alt_tag")); assetDetailList.Add(assetDetail); dataReader.NextResult();// this is the 2nd result set AssetContent assetContent = new AssetContent(); assetContent.content_id = dataReader.GetInt32(dataReader.GetOrdinal("content_id")); how do I creatre another list .Add(assetContent); } // Close and dispose of the reader dataReader.Close(); dataReader.Dispose(); } #endregion }
View 2 Replies View RelatedOur parts table has 5k records. I want to use part number as a parameter for one of my reports. Is there a way to do this and have the report generate in a reasonable amount of time?
Thanks
I would like to display a couple of records horizontally via a table or list, but don't think this is possible. Am I right? The given recordset should only have two records and the formatting would be much better if they were displayed left to right rather than one on top of the other. Is this just not possible with SSRS? Thanks,
Levi
Hello,
I`ve created a table in a list that is grouped by years. Result is a Report, that consists of several years (List grouped by years, each containing the table with the data of the year).
Now I want to aggregate some numbers of the table on each page (year) of the list.
The sum()-function - doesn't matter which scpoe I use - always aggregates only the values of the actual list (Year), not considering the years before.
Can anyone help?
Thanks a lot -
Markus
I was under the impression that Management Studio wouldn't list any databases that a user doesn't have access to. But if I create a new user and give them db_owner permissions to just one database, if they log into Management Studio, they are able to see all databases listed. They don't have access to do anything further, but they can still see the list which I thought was handled better in 2005.
View 8 Replies View Related
I want to display a phone directory by department.
I created a list and told it to group on department and then I put a table in the list that had PhoneNo, EmployeeName as the columns.
Now the list works in that it seperates that table out by department (so 5 departments means 5 instances of the table with a text box above the table but inside the list with DepartmentName.Value).
The problem though is that text box only appears once for each table, the first time the table is generated. I can't get the department name to display at the start of each page.
Do I need another sublist or something, how can I get the department name textbox to appear at the start of each page for my list?
So what I have is:
DEPARTMENT NAME: Sales
---(table starts here)
.............5555...Smith
.............lots more names.......
- - - - - - - - - - - - - - - PAGE BREAK - - - - - - - - - -
---(table continues on from last page...)
.............4444...Johnson
.............3246...Benson
---(table ends here)
------------------------------------new iteration of this, so the next department is IT and this all repeats again
What I want to get is:
DEPARTMENT NAME: Sales
---(table starts here)
.............5555...Smith
.............lots more names.......
- - - - - - - - - - - - - - - PAGE BREAK - - - - - - - - - -
DEPARTMENT NAME: Sales (this is repeated as it is the start of a new page, in a perfect world I'd be able to even say "Continued..." after the department name on all pages that were not the first page it appeared)
---(table continues on from last page...)
.............4444...Johnson
.............3246...Benson
---(table ends here)
------------------------------------new iteration of this, so the next department is IT and this all repeats again with department name at the start of each and every page
I have a large report which profiles a customer and produces many pages consisting of sales records, support calls and emails, etc etc.
View 3 Replies View RelatedHello,
I'm writing a Data Processing Extension for SSRS 2005 that allows you to use a WSS 3.0 List as a dataset.
This works for one list per dataset. Now I am trying to extend the extension to allow multiple lists in a dataset because I want to join lists on a common field (INNER JOIN). The problem is that I can only return one DataTable to the Report Server with the DataReader. Does anyone know if it is possible to return multiple DataTables (without the join)?
I'm a student, working on this project. I know Visual Basic.net but only started last month with Data.
Thanks in advance
Tom
Hi
Will somebody please explain how to combine asp.net dropdown lists to write
a SQL database select query. I am using VWdeveloper and C Sharp.
For example, say I have 3 dropdownlists on my webpage as below,
List 1, Cities, London, Rome, Barcelona etc
List 2, Restaurants by Type, Italian, chinese, Indian etc
List 3, Number of tables/ seats 10-20, 20- 40, 50 -100
I want someone to be able to search for a restaurant by selecting an item from each dropdownlist
such as, "Barcelona" "Italian" "50-100"
This search query would return all the Italian restaurants in Barcelona with 50-100 tables/seats.
I would also like the select query to work even if one of the dropdownlists items is not selected.
Hope somebody can clear this up?
Also would sql injection attacks be a threat by doing it this way?
Thanks all
I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!
Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?
If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.
Thanks in advance for any replies, they would be GREATLY appreciated :)
I have gridview bound with SqlDataSource control. One of the grid columns is a dropdown list which is populated programatically, (the gridview has template column with edittemplate field with dropdown list), dropdown Value contains of course collection of IDs.Please tell me how to declare UpdateCommand which will update the row with the dropdown list. The problem is with parameter regarding the column with dropdown list. When i declare this parameter and bind it to SelectedValue property of the dropdownlist from edittemplate tag - it doesnt work, because datasource control cannot see this dropdown list. So, how am i supposed to declare the ID of the row being updated in <updateParameters>? Thanks in advance!
View 4 Replies View RelatedHow do i add two values of Dropdownlist 1 and 2 to create No.3
Is there an easy way through Visual Studio to do this than Realms of code that i have been reading online,
Surely, there is some command that is like Select Distinct Name from Clients WHERE city=@city + country=@country
Or is it not this easy?
Hi,
I wrote the following function in an ActiveX script task in the database transformation. When I sent it to my own email address, it sent out the message fine, but when I change it to "SP Directors" which is a distribution list, and execute this step, it gave the error stated: "ActiveX Scripting encountered a Run Time Error during the execution of the script." Any one can help me on this problem, thank you so much in advance.
Function Main()
dim eMailString
eMailString = "xxxxxxxxx test"
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Test " & Time()
objMessage.Sender = "js@comcast.com"
objMessage.To = "SP Directors"
objMessage.TextBody = eMailString
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.iparty.com"
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.Send
Main = DTSTaskExecResult_Success
End Function
CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')
The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.
The results I need are:
CATEGORYID STARTRANGE ENDRANGE
1000 S01 S03
1000 S09 S20
1000 S22 S24
1001 S11 S12
1002 S30 S32
1002 S34 S35
1002 S60 S60
1003 S40 S40
I have tried taking the original data and parsing it out as an XML file. Is there a less cumbersome way to do this in TSQL?
How to write Aggregate functions for tables and lists as If I can write them many problems in my reports will be solved. I tried writng it in the filters but I got an error saying Aggregate functions are not allowed for tables and lists. Can any one help me in this regard?????
View 4 Replies View RelatedHi All,
I need to do data transfer from three tables in SQL server 2005 to the single Excel sheet as a report. Even If I create a Lists or named range, I couldn't able to transfer the data into the Lists. The idea is, whenever the data is transfered into the Lists, the three Lists (or Range) having data should grow separately.
Currently the data is transfered out of the Lists.
Anyone can help me.
Thanks
Sanra
Are groups within a list or table forced to be kept on the same page (if possible)? If so, is there any way around this?
My problem is a report with a list with 3 tables inside the list. All tables show details associated with the grouped individual. The size of one list item can be slightly smaller than half a page or over. When the report can not fit two list items on one page, it starts a new page and leaves unwanted white space.
I have tried:
adding a rectangle
adjusting width, margins, etc.
not using lists, only tables nested within tables
to check for page breaks, I adjusted the page size to 8.5 X 20. Same thing occurs, as many lists as possible on one page without splitting between pages.Thanks for any guidance.
Does anyone else experience Visual Studio 2005 shutting down when they try to preview a report which has a subreport contained in a table or list?Error Log:Description:Faulting application devenv.exe, version 8.0.50727.762, stamp 45716759, faulting module kernel32.dll, version 5.1.2600.2180, stamp 411096b4, debug? 0, fault address 0x0001eb33.Description:Bucket 358802311, bucket table 1, faulting application devenv.exe, version 8.0.50727.762, stamp 45716759, faulting module kernel32.dll, version 5.1.2600.2180, stamp 411096b4, debug? 0, fault address 0x0001eb33.
View 1 Replies View RelatedI'm trying to list out my a multi-value parameter in a table or list. Is this possible?
So far I've tried the simple do-it-the-same-way-as-a-dataset approach, and that doesn't work because it comes back with an error saying I need a dataset in my table.
I then tried creating a dataset that's identical my multi-value parameter, but I was stumped when it came to creating the correct SQL. I am able to pull the last value of my parameter into a dataset with a simple SELECT @MyParam query, but that's not going to cut it...
Any more ideas? Thanks!
I once had a DBA tell me the ancient wisdom of the different ways of searching through lists and the performance impact of each type. Does anyone know this secret?
I think the best performance was SELECT * FROM temp WHERE temp.acctid IN (1123,1234,1235)
The worst performance I think would have been correlated subqueries. Then I can't remember where temp tables and embedded queries ranked.
Thanks
I have multiple drop down lists that I would like to have send parameters to a stored procedure that does a select on the data. (Kind of like a search feature) But if a ddl isnt populated, I want that to act like a * and get all. For instance, I have a persontype, state, and country drop down lists. If I drop the state one down to WA, I want the gridview to populate with all the people in WA regardless or their type. If I populate type and state, have it give me both regardless of country.
What does the stored procedure or select syntax look like for this? (What would the default values of my dropdown boxes?)
my current sql select statement (The bottom 3 are bit fields which are throwing me off a bit too). do i need to have some IsNull statments around each variable in the select?SELECT
PersonFirstName
, PersonLastName
, PersonStreetAddress
, PersonCity
, PersonZip
, PersonEmail
, PersonEmailDate
, PersonPhone1
, PersonNotes
, InterestChapterLeaders
, InterestMembers
, InterestUniLeader
, PersonGradYear
, PersonTypeID
, CountryID
, StateID
, SchoolID
, PersonID
FROM tblPerson
WHERE
tblPerson.PersonTypeID = @PersonTypeID
and tblPerson.StateID = @StateID
and tblPerson.CountryID = @CountryID
and tblPerson.SchoolID = @SchoolID
and tblPerson.InterestChapterLeaders = @InterestChapterLeaders
and tblPerson.InterestMembers = @InterestMembers
and tblPerson.InterestUniLeader = @InterestUniLeader
Below is the code for two data sets and I can't seem to get my head around the issue. I need to find the number of 'ER' visits and 'IN' visits, separately, in dbo.VisitData for the 'Active' patients in dbo.PatientStatus. So, consider patient 69. He is Active on 5/5/2014 but becomes Inactive on 9/15/2014. I only want to count the number of visits ER or IN that are between those dates. In addition if patient 69 becomes active again after 9/15/2014, I need to capture that data as well. Patients can change there status multiple times.
create table dbo.PatientStatus
as
(
patient_id varchar(10),
status_type varchar(10),
status_date datetime
[Code] ....
I am trying to grant access to groups on my reports... The thing is that, whenever I try a distribution list I would get...user or group name is not recognized... (rsUnknownUserName)
1-how can I recognize if it is a group or a distribution list in advance...
2- why can't I grant access to distribution lists?