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 have
a problem -- a complex query problem...
I can create a parameter query in a stored procedure, but how do I use
the result set of a parameter query in a select query (in the same or
another sp)? In short, if a select query contains a result table that is
generated as a parameter query, how do I pass the parameter through the
select query to the nested parameter query?
For example, if I have a sp parameter query called [e_typerows], I could
write 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.shadow
FROM bi_dictionary_e INNER JOIN
(bi_occ_01_e INNER JOIN
[e_typerows] ON bi_occ_01_e.row = [e_typerows].row) ON
bi_dictionary_e.typeid = bi_occ_01_e.typeid
ORDER BY bi_occ_01_e.row, bi_occ_01_e.pos
Even though [e_typerows] is a (storted procedure) parameter query this
will work IF my parameter in [e_typerows] has a default, say "yyyy". In
the default case no parameter needs passing. But what if it doesn't have
a 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 something
like [e_typerows @myparameter='xxxx']? (Maybe I should try that! I've
seen 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.row
FROM dbo.bi_occ_01_e INNER JOIN
dbo.bi_dictionary_e ON
dbo.bi_occ_01_e.typeid = dbo.bi_dictionary_e.typeid
GROUP BY dbo.bi_dictionary_e.typestring,
dbo.bi_occ_01_e.row
HAVING (dbo.bi_dictionary_e.typestring = @typestringparameter)
Any suggestions would be greatly appreciated! (For the moment, I first
generate the parameter query result set, then I loop through each row of
that result set and do a select query with that row as a parameter --
very complicated!, though it does work.)
I am passing the partameters to MS Report Viewer control to view the report. It is working fine with normal parameters like 'abc', 'Jon' etc. But fails with parameters having special characters like 'abc+', 'Version 1.1 Part 1', 'R1.8 RC' throws error " The parameter is missing a value"
ID | NUMBER | DSTART | DEND | ADDRESS | ---------------------------------------------------------------- 1 | 9524123 | 12 Dec 95 | 24 Dec 95 | London | 2 | 9524123 | 06 Jan 96 | 15 Jan 96 | Paris | 3 | 084521 | 12 Mar 96 | 15 May 96 | New York | 4 | 084521 | 22 Aug 96 | 25 Aug 96 | Sidney | ---------------------------------------------------------------
Now, I need to build a query to show only the latest DTSTART date for each NUMBER. The result would be something like this:
PHP Code:
ID | NUMBER | DSTART | DEND | ADDRESS | ---------------------------------------------------------------- 2 | 9524123 | 06 Jan 96 | 15 Jan 96 | Paris | 4 | 084521 | 22 Aug 96 | 25 Aug 96 | Sidney | ---------------------------------------------------------------
Can you guys help me to build the queries? Thanks in advance.
Hi, I have been trying to write a nested query, however i am facing errors while executing that. The code is as below. <% Dim MyConnection = New SqlConnection() MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString MyConnection.Open() Dim cmd As New SqlCommand Dim rs As SqlDataReader Dim rs2 As SqlDataReader Dim query = "select count(bank_id) from bank_master" Dim query2 As String cmd.CommandType = CommandType.Text cmd.Connection = MyConnection cmd.CommandText = query rs = cmd.ExecuteReader If rs.Read() Then Dim rec = rs(0) If (rec > 0) Then Response.Write(rec & " banks registered in the system") rs.Close() query2 = "select * from bank_master order by bank_id" rs2 = cmd.ExecuteReader(query2) If rs2.Read() Then Response.Write(rs2(0)) Else Response.Write("No banks yet registered in the system") End If Else Response.Write("No banks yet registered in the system") End If End If %>
The error message that i am getting at runtime:
Server Error in '/Accounts' Application.
Input string was not in a correct format. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: Input string was not in a correct format.Source Error:
Line 117: Line 118: query2 = "select * from bank_master order by bank_id" Line 119: rs2 = cmd.ExecuteReader(query2) Line 120: If rs2.Read() Then Line 121: Response.Write(rs2(0)) Source File: E:WEBHOMEAccountsank.aspx Line: 119 Could you please help?
and want to produce a query to show the with following
All Grouped by [ToDoReferralID] Count [ToDoID] AS [ToDoToTal] Count [ToDoID]WHERE [ToDoCompleted] IS NULL AS [ToDoIncomplete] Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] <= GETDATE() AS [IncompleteUTarget] Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] > GETDATE()AS [IncompleteOTarget]
I can manage a single aggregate but where to being trying to incorporate all into one query.
All: I am trying to code the following SQL into an OLEDB data source but it is not allowing me to do so because I think the variables are nested in multiple SQL statements. I have seen other posts that suggest using a variable to store the SQL but I am not sure how it will work.
I would also like to mention that the OLEDB source executes from within a For Each loop that is actually passing the values for the variables, which was one of the reasons I got stumped on how I could have a variable store the SQL.
Here is the SQL:
select b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor,sum(a.Ordered) As Qty_Pruchased From SXE..POLine a INNER JOIN (SELECT VIR_Program.ProgramID, VIR_ActiveSKU.ProductCode, VIR_ActiveSKU.BuyerID, Vendor FROM VIR_Program INNER JOIN VIR_ActiveSKU ON VIR_Program.ProgramID = VIR_ActiveSKU.ProgramID INNER JOIN Vendor ON VIR_Program.VendorID = Vendor.VendorID WHERE ProgramFreq=?) b ON a.ProductCode = b.ProductCode WHERE a.TransDate >=? AND a.TransDate ? Group By b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor
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 .
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 am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab SELECT cohort.PatientSID ,SubQuery1.LabChemResultNumericValueAS 'A1c%' ,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date' ,SubQuery2.LabChemResultNumericValueAS 'LDL'
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
We have a parameter-driven OLAP report running inside a ReportViewer in an ASP.NET application. It runs very slowly, but you run the MDX directly against the cube (replacing parameters with actual values) it works fine.
The problem is an inherent inefficiency with the StrToMember function in the MDX. To get round this we would like to find a way of programmatically collecting the parameter from the user, building the MDX query in code and passing it to the report.
Is it possible to pass a pre-built MDX string to a ReportViewer which can then be used as the querystring for the dataset?
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.
Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.
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, how to pass paremeters in sqldataadapter? here iam checking authentication using sqlcommand and datareader like that if iam checking authentication using dataset and sqldatareader how to use parameters?
SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz;data source=server"); cn.Open(); // string str = "select username from security where username='" + TextBox1.Text + "'and password='" + TextBox2.Text + "'"; string str = "select username from security where username=@username and password=@password"; SqlCommand cmd = new SqlCommand(str, cn); cmd.Parameters.AddWithValue("@username", TextBox1.Text); cmd.Parameters.AddWithValue("@password", TextBox2.Text); SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows) { Session["username"] = TextBox1.Text; FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, false);
} else { Label1.Visible = true; Label1.Text = "Invalid Username/Password"; } ------------------------------------------------------------------------------------- like that if iam checking authentication using dataset and sqldatareader how to use parameters?SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz; data source=server"); cn.Open();ds = new DataSet(); string str = "select username from security where username=@username, password=@password";da = new SqlDataAdapter(str, cn); //how to pass parameters for that da.Fill(ds, "security"); Response.Redirect("dafault2.aspx");
Still need help passing a criteria parameter query from a SQL Function (i.e. @StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].
If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
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.
A little new to ASP.NET pages, and I'm trying to pass some parameters to a SQL 2000 Server using a TableAdapter, code is as follows: ------ TestTableAdapters.test_ModemsTableAdapter modemsAdapter = new TestTableAdapters.test_ModemsTableAdapter(); // Add a new modem modemsAdapter.InsertModem(frmRDate, frmModem_ID, frmProvisioning, strDecESN ); -------- And the error I get when loading the ASP.NET page is as follows: Compiler Error Message: CS1502: The best overloaded method match for 'TestTableAdapters.test_ModemsTableAdapter.InsertModem(System.DateTime?, string, string, string)' has some invalid arguments --------------- Now I realized the four strings that I am trying to pass to the server refer to ID's on Textboxes on the web page. Not sure if that might be the problem for databinding... ? Or is it my statement for the adapter? -Ed
The SQL for a dataset in ASP.NET 2.0 is as follows.... SELECT DISTINCT StudentData.StudentDataKeyFROM Student2Roster INNER JOIN StudentData ON Student2Roster.StudentDataRecID = StudentData.StudentDataRecIDWHERE (Student2Roster.ClassRosterRecID IN (@ClassRosterRecIDs) ClassRosterRecIDs are giuds At design time i use 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9' as the parameter and it works in the designer but not at runtime. At runtime I get the error Conversion failed when converting from a character string to uniqueidentifier. If I leave the quotes off and only use 1 value like b2cf594d-908b-4c0c-a67f-6364899a4d42 it works.How can I use multiple guids as an IN parameter like 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9'? Thanks
Hi I'm trying to add 2 date parameters to the where caluse but not sure how to structure it: Can anybody help? Public Function GetCategoryPerformance(ByVal PriorityType As String) As DataSet '******************************************************* 'This web service will return a specific row for a table '*******************************************************Dim cn As New SqlConnection("data source=CADTRAINING;persist security info=True;initial catalog=pathwaysreporting;user id=cad;password=cad;") Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * from sp_category_performance where where priority = '" & PriorityType & "'", cn)Dim ds As DataSet = New DataSet Try da.Fill(ds, "CategoryPerformance")Catch ex As Exception Throw New Exception(ex.Message) End TryReturn ds End Function End Class
I am executing a DTS package from an asp page using the following code. I would like to also pass DTS Global variables along. i assume this is possible but can't seem to find an example.
Set oPkg = Server.CreateObject("DTS.Package") oPkg.LoadFromSQLServer "HOFDBMCRM4","TraubGar","ripley",DTSSQLStgFlag_Default,"","","","DSC_CalculateBOS" oPkg.Execute()
I would like to pass parameters from isql command line to an input query file. I am working on SQL Server 7.0. How can I accomplish this?
My eg. is like this:
> isql.exe (all the server options etc.,) /iInputfile /oOutput file I need the output depend on the query given in the input file with the query depending on the parameter I supply on the command line.Thanks for your help in advance.
Can I pass a parameter to a view? Can something like this even be done?
CREATE view co_interlinks1 AS SELECT [TDirectorships].[IDDir], [TDirectors].[DirLName] + ', ' + [TDirectors].[DirFName] AS DirectorName, [TDirectorships].[Ticker], [TCompanies].[CompanyName] FROM TDirectorships INNER JOIN TCompanies ON [TCompanies].[Ticker]=[TDirectorships].[Ticker] INNER JOIN TDirectors ON [TDirectorships].[IDDir]=[TDirectors].[IDDir] WHERE [TDirectorships].[IDDir] in (SELECT [TDirectorships].[IDDir] FROM TDirectorships WHERE Ticker=@Ticker)--This line requires a variable value corresponding to Ticker for the chosen company and [TDirectorships].[Ticker] <> =@Ticker --This line requires a variable value corresponding to Ticker for the chosen company
Hi I created a report based on category,time and stores as report parameters.The datasets are created by using mdx queries and the provider I selected is olap services.I would like to add a bar chart in the layout and the resultant chart should be based on the selected parameters.I am not able to get this.First of all is it possible? If so, what should I do in the chart properties?