Hi,
I create a view in SQL Server Enterprise Manager - View Design, something like this:
Select customer, name from scheme.custm Where customer=?
There's a SQL Syntax error message - "Parameters cannot be used in this query type."
When I run a SQL query in SQL Query Analyzer as:
Create VIEW view_customer As Select customer,name from scheme.custm Where customer=?
There's an error as:
"COUNT field incorrect or syntax error."
Hi everybody,this view runs just fine but when I try to save it I have an error message:ODBC error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect ofsyntax error. Where is the problem? I use questionmark because I needunnamed parameter veiw.select ProductID, NameOfProduct from dbo.Products where ProductID < ?Thank you in advance,Danilo Popoviæ, B. Sc.Trgostil d.d. - ICToplièka 1649240 Donja StubicaCroatia
After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.
One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.
So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.
Hi,I have a View that groups and sums up totals.This View is then nested within in another View and used (it needs tobe done like this). What i need to do is to be able to vary therecords in the nested query by specifying dates. These would somehowneed to be passed to the nested query.I've looked into stored procedures/functions but am still stumped onhow to do this.Can anyone help?
Hi, We have recently upsized an access db to a sequel server db. The queries in access have been made into views in sql. ASP will be the front end. We are having problems passing an input from a form into a view. In access the parameter had to match what was selected on the form but sequel doesn't like it. At the moment, for testing purposes, the value has to be hard coded i.e. case_id = 64. Is there anything is ASP that can help? Thank you
In the below sproc I want to create a view based on the incoming parameter, which is a date passed on from VB in the format 01-01-2006.
Yet getting that to work, seems quite a challenge.
I've been dabbling a bit with the sproc and QA, passing the date parameter to it in varous forms, using both single, double and triple quotationmarks and changing the parameter type to datetime.
I either wind up with a syntax error " Incorrect syntax near '01' " or " Unclosed quotation mark before the character string ".
Anyone have a guess at how to fix this issue?
Cheers, Trinsan
REATE PROCEDURE KONTROL_DRdobbeltrapportering
@rapdato as varchar
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[KONTROL_vw_DRdobbeltrappbasis]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [KONTROL_vw_DRdobbeltrappbasis]
EXEC(' CREATE VIEW KONTROL_vw_DRdobbeltrappbasis AS SELECT Gramex_DW.dbo.Programhoved.[Rettet af DR], Gramex_DW.dbo.Programlinie.Stationskode, Gramex_DW.dbo.Programlinie.Udsendelsesdato, Gramex_DW.dbo.Programlinie.[Udsendelses starttid], Gramex_DW.dbo.Programlinie.Rapporteringsdato, Gramex_DW.dbo.Programlinie.Mediekode, Gramex_DW.dbo.Programlinie.MUSANummer, Replace(Left(convert(varchar, [Gramex-ID]),11),''.'','''') AS GramexID, Gramex_DW.dbo.Programlinie.Side, Gramex_DW.dbo.Programlinie.Tracknummer, Gramex_DW.dbo.Programlinie.ISRC, Gramex_DW.dbo.Programlinie.Producentlandekode, Gramex_DW.dbo.Programlinie.Spilletid, Gramex_DW.dbo.Programlinie.Minuttakst, Gramex_DW.dbo.Programlinie.[Afregnet Station], Gramex_DW.dbo.Programlinie.[Afregnet den], Gramex_DW.dbo.Programlinie.[Afregnings ID], Gramex_DW.dbo.Programlinie.[Ur-opførelse], Gramex_DW.dbo.Programlinie.Talestof, Gramex_DW.dbo.Programlinie.[Betalt af DR], Gramex_DW.dbo.Programlinie.Kending, Gramex_DW.dbo.Programlinie.[Oprettelses ID], Gramex_DW.dbo.Programlinie.[Oprettet den], Gramex_DW.dbo.Programlinie.[Oprettet af], Gramex_DW.dbo.Programlinie.[Ændret den], Gramex_DW.dbo.Programlinie.[Ændret af], Gramex_DW.dbo.Programlinie.[Eventuel delskæring], Gramex_DW.dbo.Programlinie.Plademærkenavn, Gramex_DW.dbo.Programlinie.Katalogbetegnelse, Gramex_DW.dbo.Programlinie.Tracktitel, Gramex_DW.dbo.Programlinie.Selskabsnummer, Gramex_DW.dbo.Programlinie.Indspilningsår, Gramex_DW.dbo.Programlinie.Fonogramtitel, Gramex_DW.dbo.Programlinie.Plademærkenummer, Gramex_DW.dbo.Programlinie.[Antal opførelser], Gramex_DW.dbo.Programlinie.[Rec nummer], Gramex_DW.dbo.Programlinie.Indslagsnummer, Gramex_DW.dbo.Programlinie.[Afspillet gange], Gramex_DW.dbo.Programlinie.[Afspillet minutter], Gramex_DW.dbo.Programlinie.Spilletidsløbenummer, Gramex_DW.dbo.Programlinie.Pointtildelt, Gramex_DW.dbo.Programlinie.[Rettet af Gramex], Gramex_DW.dbo.Programlinie.[Pgmkorlinie løbenummer], Gramex_DW.dbo.Programlinie.[Tape Kilde], Gramex_DW.dbo.Programlinie.[Hoved artist], Gramex_DW.dbo.Programhoved.Produktionsnummer FROM Gramex_DW.dbo.Programlinie INNER JOIN Gramex_DW.dbo.Programhoved ON (Gramex_DW.dbo.Programlinie.Stationskode = Gramex_DW.dbo.Programhoved.Stationskode) AND (Gramex_DW.dbo.Programlinie.Udsendelsesdato = Gramex_DW.dbo.Programhoved.Udsendelsesdato) AND (Gramex_DW.dbo.Programlinie.[Udsendelses starttid] = Gramex_DW.dbo.Programhoved.[Udesendelses starttid]) WHERE Gramex_DW.dbo.Programlinie.Rapporteringsdato >= ' + @rapdato + ' AND (Gramex_DW.dbo.Programlinie.Mediekode <> 1) AND (Gramex_DW.dbo.Programlinie.Spilletid >= ''00:01:00'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 ZULU'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 CHARLIE''') GO
I am working on SSRS. Based on parameter value I needs to call the related view. Below are my current expression query. but it has some error.
=IIF(Parameters!RacServiceOfferingsOnly.Value=0, SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_N WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate) AND (:TransferType IS NULL OR :TransferType = "Direction"),SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_Y WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate))
After connected to Analysis Services and following the path below:
Analysis Service - Databases - Cubes
I create the parameters for a custom report after right click on a specific Cube and select Browse to arrive at a view where i can define dimensions and parameters as well as drag and drop measure to the report rows and columns. The left top File bottom only give me a Save Selection or Save ALL option. However, i'm not sure where it saves it to or if it saves the settings for this custom report at all.
I pull this report monthly and it's a pain to recreate all the parameters. How to save this template for future pulls or maybe even incorporate the pull into excel for monthly refresh instead?
Has anyone encounted a problem with Reporting Services (2005) with the following scenario: A report with a dropdown parameter allowing a Select All choice -
If the selection list is fairly long and Select All is selected - if the user clicks the View Report button the selection list is completely reset (i.e. all items are unselected). This seems to happen if the user quickly clicks the View Report button immediately after clicking the Select All box on the parameter dropdown.
This happens inconsistently - some users experience it frequently while others do not see it at all (using the same parameter values, etc.)
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chartÂ
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1 available values: from query default values: non query (specify a value "<None>") Parameter 2 available values: Non query (no value specified) default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
I have parameters in my report. The user can choose the year, month and date (3 parameters). Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension . Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007". But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All". Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
CREATE VIEW dbo.vwFeat AS SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4 FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application. Thanks for help
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature  available in any other updated version of ssrs?Â
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.Â
Is this a bug in Visual Studio? How do I get around this?Â
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment ( @startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())), @enddate Date = (Convert(Date,@StartDate-90) ) RETURNS TABLE AS RETURN ( SELECT EquipID, EmpName, IssueDate FROM HR.Equipment WHERE IssueDate <=@StartDate and IssueDate >=@EndDate ) GO
