Performance Gain On Specifying SqlDbType On Parameter
Jun 28, 2004How much if any of a perf gain is there when you specify the SqlDbType on a parameter?
View 1 Replies
ADVERTISEMENT
Why The Big Performance Gain?
Dec 10, 2007Hi All
I am trying to figure out why i recieved such a performance gain by separating a sproc into two separate transactions (data integrity is still ok)
Before when it was 1 tran, my proc was flowing something like this:
BEGIN TRAN
INSERT into work tables (2 cursors and 1 straight insert)
INSERT into data warehouse
FROM joins to work tables and other transactional tables.
COMMIT TRAN
now it is:
BEGIN TRAN
INSERT into work tables (2 cursors and 1 straight insert)
COMMIT TRAN
BEGIN TRAN
INSERT into data warehouse
FROM joins to work tables and other transactional tables.
COMMIT TRAN
The first sproc was running in 18 min and I was constantly seeing the spid blocked (and sleeping) by the same spid.
Now when using two separate transactions it runs in a minute and a half.
What gives? I am dying to know the explaination so i can plan better in the future. This is running on SQL 2000.
Thanks!
Cam
SqlDbType.Int, 4 - Does Specifying The Parameter Datatype Size Eventually Stop Auto Increment In The Database
Jun 17, 2008Hi, I'm specify the size of the interger dataypes in my code behind files; e.g.
commad.Parameters.Add("@SomeID", SqlDbType.Int, 4)
I know it may seem silly, but I have to ask:
Will specifying ths size (i.e. 4) eventually stop the auto-icnrement of the underlying primary key SomeID in the SQL Server 2005 database?
What restriction, if any, does this place if the next SomeID value is 10000, which is 5 digits? or 999999, which is 6 digits?
Thank you
Where To Gain The Information About Sp_MSforeachtable .....
Sep 26, 2000Hi,
If anybody know the source (links) with information about SP like this one.
sp_MSdependencies and so on.
thanks.
Best Book To Gain Good Knowledge In DBA
Feb 26, 2007Dear Friends,
please suggest me the best book to gain sound knowledge in DBA concepts as well as
writing in sub queries, complex queries as well as inner queries.
thank you very much
Vinod
Gain Access To Locked Server
Jun 19, 2007Am I imagining it or did I read somewhere once about an tool/command/method that allows the administrator/dba to access the server even if no-one else can - probably only in certain scenarios.
I think it had a use for a situation where all the connections are used up and no further connection pooling can kick in for a while. This tool enabled the dba to still get to the server and I presume still be able to run stuff like sp_who and kill.
Does this ring a bell with anyone?
Getting SqlDbType Not Declared
Nov 6, 2007How do I dim SqlDbType in my code?Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)
Dim cmd As New Data.SqlClient.SqlCommandWith cmd
.Connection = conn
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "UpdateTopicscmd.Parameters.Add("@classificationID", SqlDBType.Int)cmd.Parameters.Add("@TitleID", SqlDBType.Int)
conn.Open()
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Thencmd.Parameters("@classificationID").Value = item.Valuecmd.Parameters("@TitleID").Value = DropDownList1.SelectedValue
cmd.ExecuteNonQuery()
End If
Next
conn.Close()
End WithEnd Sub
End Class
Sqldbtype Numeric
Mar 22, 2006Hello,Im calling a stored proc that has a numeric value as a varible.In the asp im using the code:myPrivate.Parameters.Add("@memberID", SqlDbType.Decimal, 18).Value = Decimal.Parse(uxToText.Text);I'm using decimal as there is no numeric sqldb type, is this correct? Also is the decimal parse correct as Im getting a 'Input string was not in a correct format' error.Thanks
View 2 Replies View RelatedSqlDbType Chars's And VarChar's
Jun 19, 2006I have an overloaded method in my data access layer that I use to load the parameters into a command object. One of these overloaded methods is as follows:
public void LoadSqlParameter(string name, string value, ParameterDirection direction){ param = new SqlParameter(); param.ParameterName = name; param.Value = value; param.Direction = direction;
param.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(param);}
Will using a SqlDbType of VarChar cause me any problems if the actual parameter in the stored procedure is a Char?
Am I better off leaving out the line that specifies the type?
Thanks
SQLDBType.Text, 2147483647
Dec 12, 2006Hi,
I am creating a parameter of type text, and using either of the 3 statements below, to specify the size of the text. Unfortunately none of the statements put all the text into the database.
The receiving column is Text(16) and Text in Row is turned off.
I really would appreciate any help with this.
Many thanks,
Polly Anna
sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.Text) ', 2147483647, ParameterDirection.Input)
or
sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.Text)
or sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.VarChar, 4000)
then
sqlParam.Value = Me.ResponseText
.net 1.1 SqlDbType Accepts Varchar(MAX)
Jun 15, 2007My app uses .net 1.1 and MS SQL 2005 as backend. How to add a SqlParamter that is varchar(MAX)?SqlParameter myParameter = new SqlParameter("@Description",SqlDbType.VarChar,whatToPutHere?,ParameterDirection.Input, true,0,0,"Whatever",DataRowVersion.Current,"Whatever");
View 4 Replies View RelatedSqlDbType.Decimal Rounding Up
Nov 15, 2007I have a field in my SQL Server 2005 database of type numeric(18,3)In code, I treat the value as decimalWhen creating my command parameters, this is how I'm declaring them:prm.SqlDbType = SqlDbType.Decimal;prm.Precision = (byte)int.Parse("18");prm.Size = int.Parse("0");prm.Scale = (byte)int.Parse("3");Inserting a number like 5.687 is rounding to 6.000 anyone know why it is doing that?
View 5 Replies View RelatedHow To Retrieve SqlDbType.Bit In SQL Server?
Sep 29, 2004Hi,
My checkbox status is saved as SqlDbType.Bit in MS SQl Server database.
When I use the following code to retrieve it, it does not work.
myRow[myColumn].ToString()=="1"
I appreciate your kind assistance.
Thank you.
How To Asssign String Variable To SqlDbType
Nov 17, 2007i have string variable as,
String str="Int";
now, while assigning sql parameters, i want
param.SqlDBType=SqlDBType.Int;
but, value of Int is dynamic. it may be string or double so,
i want it to be as,
param.SqlDBType=(SqlDBType)str;
but its not acceptable(its invalid cast).
in any way can i do it and how?
regards---
How To Handle System.Data.SqlDbType.Text.
Apr 14, 2006I am trying to insert a large amount of data from a web form I created into a "text" field in my DB. Problem is its oly entering the 1st 16 characters. I can see why but I dont understand what I need to do to avoid this shortcomming. Ive searched the forums here and in google groups to no avail :(
<code>
sqlCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Content", System.Data.SqlDbType.Text, 16, "Content")).Value = ftbContent.Text
</code>
How do I need to restructure the above line?
Any help would be appreciated.
TIA,Stue
SqlCEParameter With SQLDBType.Binary Won't Allow Size To Be Set &&> 510 Bytes
Nov 1, 2006Here is my code:
Dim oParameter As New System.Data.SqlServerCe.SqlCeParameter("@pMyParameter", SqlDbType.Binary, 3000)
If you set a watch on this object, the size is set back to 510. I have tried resetting the size back to 3000 after construction using oParameter.Size, but it doesn't change from 510. If the command is executed using ExecNonQuery, this causes the bytes to get cut off at 510 bytes and returns the error: Byte array truncation to a length of 510.
Can I insert data into SQL Server 2005 Mobile Edition, into a field of data type binary(3000) using .NET CF 2.0 via SqlServerCe objects?
Thanks for any help.
Kirk
Passing Boolean To Stored Proc As SQLDBtype.bit Not Working
Mar 17, 2004Hi I was hoping that someone might be able to help me with this.
I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0
myParm = myCommand.Parameters.Add("@bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)
I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.
Thanks in advance for any advice.
Reporting Services :: Multiple Valued Parameter Passing In SSRS For Getting Performance
Aug 13, 2015we are using SSRS 2012.Oracle 9i as back end database. Select A,B,C from view where A in (Param1) in above query when I am passing 1 value getting output in 30 sec. when I passed two values getting time out error in SSRS. how to pass multiple values in Oracle 9i in SSRS report.
View 4 Replies View RelatedHow To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date
Sep 21, 2006HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies View Related[Performance Discussion] To Schedule A Time For Mssql Command, Which Way Would Be Faster And Get A Better Performance?
Sep 12, 20041. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
Extremely Poor Query Performance - Identical DBs Different Performance
Jun 23, 2006Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedVery Poor Performance - Identical DBs But Different Performance
Jun 22, 2006Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies View RelatedRS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It
Feb 19, 2007Hello,
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!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
Reporting Services :: Preview Has One Extra Parameter Than Parameter List - SSRS
Aug 21, 2015I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
Reporting Services :: Multi-value Parameter Not Passing In Subreport Parameter List
Jul 29, 2015I 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 .
Default Parameter Value Is No Longer Part Of Cascading Parameter In SSRS 2005?
Jan 30, 2007Hi,
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?
Thanks.
Kong
How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters
Oct 15, 2007
Hi,
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.
Thanks !
JF
Reporting Services :: Share Point List Data Set Parameter Allow Null In Multivalue Drop Down Parameter
Aug 28, 2015my 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.
View 3 Replies View RelatedOLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter
Apr 17, 2008Does 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 Dynamically Populate A Parameter List With Values Based On Another Parameter Value?
Aug 11, 2005Is 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
Reporting Services :: Hide / Unhide A Parameter Based On Another Parameter?
Oct 18, 2015I 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?
View 2 Replies View RelatedTwo Parameters Question? Second Parameter Should Get Data Based On First Parameter Selection.
Jan 8, 2007I have two parameters both are related to each other.
second parameter should get filled based on the selection of the first one which is project.
the first paramater is project, once the project is selected it should bring the all the contracts related to that project.
Please is it possible...
Thank you very much for all the helpful info.
Stored Procedure With User!UserID As Parameter, As Report Parameter?
Jul 2, 2007I 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