I have a question about using Parameters in queries. I created a list of parameters that the user can select from. For example one is MS07%. The other parameter is a list of locations that is built from a query. These parameters are then being used in a query to build a dataset. When i try to preview the report I select parameters the I know work because I tested the query with them, I get an error that says "Must declate the scalar variable "@Param1". I'm not really sure what that means. Any help would be appreciated.
Hi all, I have a MDX query that has been put together from a Analysis Services Cube in Reporting Services, as below: SELECT NON EMPTY { [Measures].[MM Document Count], [Measures].[FI Document Total], [Measures].[MM Document Total], [Measures].[FI Document Count] } ON COLUMNS, NON EMPTY { ([Lookup Company Code].[Company Code].[Company Code].ALLMEMBERS * [Lookup Financial Period].[Financial Year].[Financial Year].ALLMEMBERS * [Lookup Financial Period].[Financial Period].[Financial Period].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LookupCompanyCodeCompanyCode, CONSTRAINED) ) ON COLUMNS FROM [SAP Document Analysis Doc Totals MM FI]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSIt has a parameters @LookupCompanyCodeCompanyCode, which creates the following dataset query: WITH MEMBER [Measures].[ParameterCaption] AS '[Lookup Company Code].[Company Code].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Lookup Company Code].[Company Code].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Lookup Company Code].[Company Code].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Lookup Company Code].[Company Code].ALLMEMBERS ON ROWS FROM [SAP Document Analysis Doc Totals MM FI] Within the Report Parameters it is set with as being a Multi value parameter and default value for return all records: [Lookup Company Code].[Company Code].[All] This work fine when previewing report & when I build. Problem is when I drop a report viewer control in VWD 2005 and attach this report, it does not run with the default values when the page loads. Does anyone have a idea what I might be doing wrong? or/and how would i pass a value from a listbox extbox to the parameter and then run the report? Thanks in advance Kevin
I have an sql query that has specific criteria (like state='PA' orstate = 'NJ'...) and would like to be able to have the user specifythe criteria dynamically either through the web or from MSAccess oranother tool.The query also does a GROUP BY the state and other variables that arepart of the criteria.I know how to get MSAccess and asp pages to do the sorting andselecting against an SQL tbl or view, but when access queries the sameinfo as the original sql view, the process takes much longer than whenthe sql view does all of the sorting, selecting and grouping..The table we are currently using is 5 million records and will begrowing to 250 million records shortly, so speed is of the essence.The sql views and MSAccess are both running from the same server sothere is no issue at this point of a network impacting the MSAccessquery.Any suggestions...
I want to create a web page where a user can select from 1-100 fields to include in output, and then query a table to only return the fields the user has selected.
I do not want to construct a dynamic SQL SELECT statement. I would rather use a stored procedure, but am not certain how to only return the fields that the user selected.
Though I am a novice to MS SQL server (2000 I believe), I can do almost!everything I need. Maybe not efficiently, but usefully. However, I havea problem -- a complex query problem...I can create a parameter query in a stored procedure, but how do I usethe result set of a parameter query in a select query (in the same oranother sp)? In short, if a select query contains a result table that isgenerated as a parameter query, how do I pass the parameter through theselect query to the nested parameter query?For example, if I have a sp parameter query called [e_typerows], I couldwrite the following select query which uses the (single) result set from[e_typerows] plus other tables or views:SELECT TOP 100 PERCENT bi_occ_01_e.row, bi_occ_01_e.pos,bi_dictionary_e.typestring, bi_occ_01_e.shadowFROM bi_dictionary_e INNER JOIN(bi_occ_01_e INNER JOIN[e_typerows] ON bi_occ_01_e.row = [e_typerows].row) ONbi_dictionary_e.typeid = bi_occ_01_e.typeidORDER BY bi_occ_01_e.row, bi_occ_01_e.posEven though [e_typerows] is a (storted procedure) parameter query thiswill work IF my parameter in [e_typerows] has a default, say "yyyy". Inthe default case no parameter needs passing. But what if it doesn't havea default value or if I need to pass a parameter to it?Is there any way to replace[e_typerows]in the above query with somethinglike [e_typerows @myparameter='xxxx']? (Maybe I should try that! I'veseen nothing about it though.)My parameter query, [e_typerows], looks like this in fact:@typestringparameter varchar(100) = "yyyy"SELECT dbo.bi_dictionary_e.typestring, dbo.bi_occ_01_e.rowFROM dbo.bi_occ_01_e INNER JOINdbo.bi_dictionary_e ONdbo.bi_occ_01_e.typeid = dbo.bi_dictionary_e.typeidGROUP BY dbo.bi_dictionary_e.typestring,dbo.bi_occ_01_e.rowHAVING (dbo.bi_dictionary_e.typestring = @typestringparameter)Any suggestions would be greatly appreciated! (For the moment, I firstgenerate the parameter query result set, then I loop through each row ofthat result set and do a select query with that row as a parameter --very complicated!, though it does work.)
If I were to create a stored procedure that searches a table using (optional) multiple parameters, what would be the best way to do the search. I want to try and avoid using several "IF" statements (like IF @FirstName IS NOT NULL, etc). How would I do it, or would I just be better off using several "IF" statements? Thanks... CREATE PROCEDURE intranet_search_GetEmployeesBySearch ( @FirstName NVarChar(100), @LastName NVarChar(100), @Phone NVarChar(50), @Cell NVarChar(100), @Pager NVarChar(100), @Ext NVarChar(50), @Email NVarChar(100), @Department NVarChar(200), @Position NVarChar(100), @IsManager Bit ) AS BEGIN
I need to create a stored procedure that will have about 10-15 queries and take 3 parameters. the variables will be: @lastmonth, @curryear and @id @lastmonth should inherit Session variable intlastmonth @curryear should inherit Session variable intCurrYear @id should inherit Session id One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "' AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id") The rest of the queries will be similar and use all 3 variables as well. How can I go about this and how will queries be seperated.
I pull data from Sql Server through the query, I want to pass the region parameter to the power pivot connection query. So that I can automatically pull the required region data. The parameter should pick the value from the excel range. And also how to control this through VBA
Hi all, From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
Hi, I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId', N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question... Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too. The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like (@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Hello all, Given: string commandText = "Categories_Delete";SqlCommand myCommand = new SqlCommand(commandText, connection);myCommand.CommandType = CommandType.StoredProcedure; Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder] It seems that ADO.NET makes an implicit conversion to the valid type. If this is correct then I can move on fat dumb and happy. Anyone have any good insight? Thanks,
Hi, I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control. i want to write my own query without help of sqlDataSource control from start something like sqldatasource con=new sqldatasource; con.connection String="" then what all things will come........ ??? and please give me some poitners to some articles which help one to do the requested.
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk, eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate, l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows. But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question). Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (". But, I hate using two queries and just wonder if there's a better way around this.
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution: SELECT d.Name, d.Surname FROM Donators d, Filles f WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age) FROM Donators d, Files f WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id FROM Donators d, Files f, City c WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity FROM City c WHERE NOT IN (SELECT c.idcity FROM City c, Donators d, Files f WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int dateon DATETIME dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate())) FROM dbo.tblonenet_sdt_proj_hold WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as: rides (and shops or all) available in a certain area. rides in a certain area above a certain height rescrictions. etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
how do i make a querie that finds relations to a "part number" for models, and assemblies, but when it finds a relation of part number with a Product, it gives additional attributes for information? this code that i have so far doesn't do waht i want,, showing
Label(LabelID,Label) labelid=pk ------------------------------- Shelf(ShelfID,Shelf) shelfid=pk ------------------------------- Country(CountryID,Country) countryid=pk --------------------------------------- Track(TrackID,Track) trackid=pk ------------------------------- Artist(ArtistID,Artist,ArtistPhoto,CountryID) artistid=pk,countryid=fk country(countryid) ----------------------------------------------------------------------------------------- CD(CDID,CDTitle,CDDate,CDCover,LabelID,ShelfID) cdid=pk, labelid=fk label(labelid),shelfid=fk shelf(shelfid) ------------------------------------------------------------------------------------------------------------ CDTrack(TrackID,CDID) trackid,cdid=pk, trackid=fk track(trackid), cdid=fk CD(cdid) ---------------------------------------------------------------------------------- ArtistTrack(ArtistID,TrackID) artistid,trackid=pk, artistid=fk artist(artistid),trackid=fk track(trackid) --------------------------------------------------------------------------------------------------------- I've already created these tables but i need some aid writing these queries coz i am still a beginner in sql
Search Queries ------------------------------------------------------------ Input Artist OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 1 Group ByCDSection ------------------------------------------------------------ InputCDTitle OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 2 Group ByCDSection ------------------------------------------------------------ InputTrack OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 3 Group ByCDSection ------------------------------------------------------------ InputLabel OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 4 Group ByCDSection ------------------------------------------------------------
Ashish Amodia writes "how to create tables in ms-sql? how different opration can be perform in database table, like insert, update, delete with tables. how to create store procedure?"
Iam developing a randomizing program .which selects random rows frm the database.iam using sql as backend and asp.net as front end. i allow user to give a number say 1000, so 1000 random rows will be slected frm the database.iam using NEWID() for random selection. But how do i put the user value into query. so as to select that many random rows from the databse. please help me out on this
