Quick question guys, I remember running into this before but can't remember if I was able to complete it or not.
What is the syntax format in the 'Report Parameters' dialog box selecting 'Non-queried' in the "value " field I want to select multiple value parameters ie 001, 002, 003, 004 for one "label" Every combination I tried it's not taking. Thanks in advance
Hello,
I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.
The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?
Any help would be greatly appreciated!
SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate
In general, what is the most appropriate way to specify a "data driven" default for a report parameter. Seems to me like options are:
1. construct an expression which operates on one of the existing report datasets (possibly the same data set used to populate the defaults dropdown)
2. construct a new dataset which returns a single value which is the desired default
what's the "normal" approach?
For example, I have an MDX dataset which contains the list of dates for which data is available. There is generally some data available for today, but normally the user is interested in the most recent day with data prior to today.
So which would be the better option out of the two above (or a third option if I'm missing something!) to tell my parameter to set itself to:
"The most recent day prior to today for which data exists".
FOA, I am using the webservices of the RS2005.
I created a report with same parameters. Two of them a based on a query. The first query contains a simple select * from ... the result will be stored in the parameter @param1. The second parameter contains select * from ... where ID = @param1. So far...
If a pass the reportparameters of the report i will get a valuelist for the first query, but nothing for the second one.
How can I manage that the second parameters also gets a valuelist?
regs
Thank you for your help.
I run a website which uses SQL 2000 and VB ASP. I would like to add a section to the site which posts the most popular data being queried from my SQL server. I'm sure this is possible, but I don't know where to start. Please let me know if any of you need specifics regarding my data and set up.
Thanks again for the help! :)
JAC
Hello. I work on a proprietary software package at work that has a SQL query engine. I'm able to query tables that I know exist but I would like to know what other tables are there in the db. Unfortunately I don't know the name of the database. Is there a SQL statement that will show me the name of the database that I'm running my query on?
Thanks for your help.
I have a problem about calculating data from the table. I want to sent a row to the local variables and calculate them by C#. Thank you in advance for every suggestion.
View 3 Replies View Relatedhi please help me,i have a table queried using this sql, select name,(select count(*) from myTable a where a.name = r.name ) as Total, (select count(*) from myTable b where b.name = r.name and dnum > '1') as Used, (select count(*) from myTable c where c.name = r.name and dnum < '1') as remainingfrom myTable r group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150% this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks
View 4 Replies View RelatedHi, is there such a thing as querying a queried result?
Example, query 1 produces Result A. Result A's table is stored 'somewhere' to be queried by query 2 which produces Result B
If there is, could you kindly direct me to a website with the appropiate article on this topic?
Thanks,
-Gabian-
I have a select statement that is being processed through oSql on Sql Server 2000. There are 2 fields in the select statement that are defined in the dateabase as nvarchar(1). When I perform my select statement, they show up in the output as 4 char fields. See dataset below for example.
493575545493575545003753404A 20070805000000002007080520070805 131307269009426800000000000000000000000
493575545493575545003753404A 00000000000000000000000020010410S 131307270009426800000000000000000000000
493575545493575545003753410A 20070805000000002007080520070805 131307271009426800000000000000000000000
How do I get rid of the extra spaces in the output? I have tried using ltrim(rtrim(fielde)) to no avail. Fieldg (the S) is a nullable field and is being processed using an isnull(filedg, ' ').
The general statement is:
Select fielda, fieldb, fieldc, ltrim(rtrim(fieldd)), fielde, fieldf, isnull(fieldg, ' '), filedh from mytable
The functioality can be replicated using:
Select 'a', 'b'
---- ----
a b
(1 row(s) affected)
Any Ideas?
Thanks in advance.
Aaron
I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.
Thanks in advance.
Mark.
I need to use a non-queried report parameter to filter a dataset for a report.
The dataset column I'm filtering is numeric. The dataset is not a sproc, it's a table in SQL Server that I am querying.
The Non-queried parameter values (Multi-value) are 1, 2, 3, 4, 5, >=6.
Selecting the >=6 throws the error: "Error converting data type nvarchar to numeric."
Which sucks.
Because...when I go straight into my dataset I can filter my numeric column with the exact same values (=1 or =3 or >=6) and everything works fine. The error is only raised when I use the @Parameter in the dataset.
I've tried eveything, researched everywhere online and I can't find any guidance anywhere.
I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.
People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801
[code]....
Results
PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom
Adding a value to a non-queried default report parameter value does not update on the target server after deployment.
To recreate
1. Create a report in Visual Studio and add a report parameter with the following properties:
Multi-value is checked
Available values = "From Query"
Dataset = [create a dataset that returns a table w/ a Id and Description column]
Value field = [the Id field from the table]
Label field=[the Description column from the table]
Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)
2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.
3. Go back to Visual Studio and add a value to the Non-queried Default values.
4. Build and deploy the report again. View the report. The newly added item is not selected.
Notes
I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.
Work-arounds
1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:
SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId
2. Delete the report in Management Studio, then redeploy.
I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.
I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file
I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.
USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]
[Code] ....
Dear all,
I have a table containing call records, and made a mining model from that table only. The model has 3 columns : calling_number, called_number, and target_operator, using Association Rule algorithm. The key is calling_number, input was operator, and predicted column called_number.
The result shows no rule, but there are results with item-set size of 1 (column) and 2 (column). On the top record of the result, SQL Server says there are 1891 support for called_number = 1891 and operator = 'INDOSAT'.
I queried the table with this query
SELECT DISTINCT calling_number
FROM call_records
WHERE called_number = '07786000815'
AND target_operator = 'INDOSAT';
It returns 2162 records instead of 1891. If I removed the DISTINCT qualifier, SQL Server returns 2159 records. Why is this differences with the result of mining?
Thank you,
Bernaridho
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)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
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.
Thanks in advance,
Scott Chang
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.
View 2 Replies View RelatedHi,
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???
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
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?
Regards,
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
Can someone explain me in detail when we use <asp:parameter> and when we use <asp:controlparameter>
Hello Dears
I Have an stored procedure like this :ALTER proc [dbo].[gl_voucher_type_insert]
@company_code varchar(3),@source_code varchar(4)
,@voucher_code varchar(4),@desc_a nvarchar(50)
,@desc_l nvarchar(50)
,@voucher_form numeric(2) ,@voucher_prefix varchar(4)
,@voucher_start numeric(8)=null
asDeclare @PrefixCount as int
set @PrefixCount=isnull((select count(*) from gl_voucher_type
where company_code=@company_code and
voucher_code=@voucher_code),0)
if @PrefixCount=0
begin
insert into gl_voucher_type(company_code,source_code,voucher_code,voucher_desc_a,voucher_desc_l,voucher_form,voucher_prefix,voucher_start)
values(@company_code,@source_code,@voucher_code,@desc_a,@desc_l,@voucher_form,@voucher_prefix,@voucher_start)
end
return @PrefixCount
ok i need in my asp page get the @ prefixCount value to make some checking on it how can i do that
please help me as soon as possible
with my best regard
khalil T.Hamad
CREATE PROC xxx
@user VARCHAR(15),
@rank varCHAR(10) AS
DECLARE @sql VARCHAR(100)
SET @sql = 'SELECT ' + @user + ' FROM usertable where grade = ' + @rank
EXEC (@sql)
GO
when i execute this proc without where condintion its working, but when i use where condition its dispalyin invalid column name with the name im passing
eg.
xxx admin,aB
WHEN I TRY TO EXECUTE PROC WITH ABOVE STAT, ITS DIAPLAYIN ERROR AS "INVALID COLUMN NAME ab
but
xxx admin," ' aB ' "
when i try like this its giving result.
how can i avoid second method of executin the proc and use first method for the sake of passing value from frontend
Hi,
The following code doesnt work. I am trying to get data from a
table according to a querystring. Id like the data in the columns
'hello' and 'hello2' to be meta name and content. But it says
The name 'hello' does not exist in the current context
command.CommandText =
"SELECT hello, hello2 FROM table WHERE ID=@ID";
command.Parameters.AddWithValue("@ID",
Request.QueryString["ID"]);
command.ExecuteNonQuery();
HtmlMeta meta = new HtmlMeta();
meta.Name = "Description";
meta.Content = "first" + hello;
Page.Title = "first" + hello2;
Header.Controls.Add(meta);
Thanks
I need to add parameters to my SQL string, like Where [EndDate] >= @HStart AND [EndDate] <= @HEnd, I tried to Dim variables but it caused an error. Can anyone help me with this?
Thank You,
Sub BindDataCurrent()
Where [EndDate] >= @HStart AND [EndDate] <= @HEnd"
'MyCommand.Parameters.Add("@HStart", SqlDbType.VarChar, 80).Value = HistoryStartText.Text
'MyCommand.Parameters.Add("@HEnd", SqlDbType.VarChar, 80).Value = HistoryEndText.Text
ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyConnection = New SqlConnection(ConnectStr)
Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= @HStart AND [EndDate] <= @HEnd"
Dim DA As SqlDataAdapter = New SqlDataAdapter(SQL, MyConnection)
Dim DS As New DataSet
DA.Fill(DS, "tblCampaigns")
MyEditDataGridCurrent.DataSource = DS.Tables("tblCampaigns").DefaultView
MyEditDataGridCurrent.DataBind()
End Sub
Hi,
I'm having a bit of trouble with SQL Parameters. I can't seem to define the type when creating the parameters.
Here's what I've done:
I have a function that processes my request and returns a datatable:
Public Function Grab_Data(querystring asn string, params() as SqlParameter) as DataTable
... dims all the necessary variables
Try
...creates the connection and command
Dim p as SqlParameter
For each p in params
p = command.parameters.add(p)
p.direction = parameterdirection.input
Next
...opens connection, creates dataset and fills it
Finall
....disposes connection and command
End Try
Return datatable
End Function
I call this function by:
...dim the necessary variables
querystring = "SELECT * FROM Tbl_Users WHERE Joined>=@date"
dataTable = Grab_Data(querystring, New SQLParameter("@date", DateTime.Today))
My code works fine and I get the results that I want, but no where in my code is the type of the parameter defined.
I tried calling the function like:
Grab_Data(querystring, New SQLParameter("@date", SQLDBType.DateTime, DateTime.Today))
But when I do this, I get an error saying that no value is assigned to @date.
Can anyone tell me what I need to modify so that I can pass the type of the parameter to the function?
The reason why I am not explicity defining the parameters in the function is because I can reusing the function numerous times throughout my code. So some calls have three parameters passed to it and some and none.
Thanks.
Hi,
I'm new in sql. How can i set the current value of parameter "MAX DEGREE OF PARALLELISM" to new value?
Thank you
Rey
Hi,
I'm trying to create a DTS transform (sql2k) with a paramterized query like:
SELECT columns
FROM table
WHERE column1 IN ?
When I try to preview this I get an error "no value given for one or more required parameters". The global variable is set and I can see the value.
Any ideas? I've been struggling with this for days with no success.
Thanks,
Hi all, I know I could do something like this in SQL:
select customers.name
from customers
where customers.name = request.form("txtname")
but my question is, can I have a user pick the operator (such as =,>,<,>=,<=) from a dropdown box and pass it the the sql statement as a parameter such as:
select customers.name
from customers
where customers.name request.form("txtoperator") request.form("txtname")
Please help!
Thanks!
Hi all, I am using classic ASP and SQL Server 2005. Can I use parameters like @whatever in my asp code or are they for stored procedures exclusively? I am trying to change the output of a request.form into a paramater so I can prevent SQL injection I am aware of using trim to counter this but I wasn't sure of the best practice.
I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.
I have a set of date parameters in reporting services which are defaulted to 3/01/08. How can I make them to the current month so that six months from now they are not still reading 3/01/08
View 5 Replies View RelatedI'm trying to do this:
ALTER PROCEDURE dbo.SkillSearch
(
@skillname char(255)
)
AS
SET NOCOUNT ON
SELECT * FROM Skill WHERE SkillName LIKE @skillname
However, when I run dbo.skillsearch 'ph%'
I get an empty set, while dbo.skillsearch 'php' returns the results expected.
I am therefore assuming that I can't use a parameter for a LIKE clause with any wildcards?
Is there any way around this other than manually building the SQL statement in the SP and then executing it? I'd obviously prefer to not have to do it that way for all the SQL Injections and related reasons.
Wouldn't this also kill my query optimization benefits, manually building the statement each time?
Thanks,
- Brian