Dealing With Large Numbers Of Parameters
Sep 11, 2007
Hi there,
I am getting a headache trying to research what to do when you have a large number of parameters to include in a query. For example, if I have a large number of checkboxes for the user to pick criteria for a report and they select several, I'm assuming it would be bad practise to say:
WHERE Field = "a" OR Field = "b" OR Field = "c" OR Field = "d" OR Field = "e" OR.....etc etc etc
Is there a good solution for this, given that the number of parameters may vary dramatically depending on what the user selects to include in a report?!
I'm running SQL Server 2000 with an ASP front end.
Any help would be greatly appreciated!
Thanks in advance!
Matt
View 9 Replies
ADVERTISEMENT
Mar 29, 2015
I have run into a problem when searching for a Movie title that is a number (like the movie name 21)
This is an access database and the Movie_Name is a text field
The SQL I am using is
Select * from Movie_Data where Movie_Title = ‘” & Movie_Name & “’”
This works perfectly for everything except when the Movie_Name = a number
Is there a way to make this search work with both characters and numbers?
View 3 Replies
View Related
Jul 20, 2005
We are looking to store a large amount of user data that will bechanged and accessed daily by a large number of people. We expectaround 6-8 million subscribers to our service with each record beingapproximately 2000-2500 bytes. The system needs to be running 24/7and therefore cannot be shut down. What is the best way to implementthis? We were thinking of setting up a cluster of servers to hold theinformation and another cluster to backup the information. Is thispractical?Also, what software is available out there that can distribute querycalls across different servers and to manage large amounts of queryrequests?Thank you in advance.Ben
View 10 Replies
View Related
Nov 23, 2005
I need to compute factorials, but I hit a limit around 170!. Is thereanyway to handle numbers larger than the float data type can handle?Thanks.
View 3 Replies
View Related
Jul 27, 2007
Hi All,
I have a large number of SPs that I would like to be able to document and provide this documentation to prospective clients. That is, provide them enough information without giving them the source code for the procedures.
I have found that all the parameters are in the sys.parameters table.
But I was wondering. Are the fields that are sent back out of an SP captured and recorded somewhere in the SQL Server catalog?
Is there an easy way to find out what fields are coming out of an SP?
Thanks in Advance
Peter
View 5 Replies
View Related
Mar 30, 2007
We are experiencing a situation where the SRS (2000 SP2) report server will no longer render reports. In the log file, there are many instances of
w3wp!runningjobs!434!3/23/2007-10:12:57:: i INFO: Adding: 8 running jobs to the databasew3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
What could be causing this? The reports are making queries through an OLE DB provider. There are no scheduled jobs, and the load doesn't seem that heavy.
View 1 Replies
View Related
Nov 26, 2007
Hello,
I have a VB.NET Windows Application with a SQL 2K5 back end.
The application creates a view which produces approximately 5 million records. I am attempting to transfer these records to a table programmatically, but every attempt causes a timeout. The name of the source server and view will change depending on the client's installation.
Using the standard SQL connection/command objects with the "INSERT INTO TABLE (SELECT * FROM VIEW)" doesn't work at all.
What is the best way to transfer these records programmatically?
Thanks,
-Torrwin
View 5 Replies
View Related
Jan 19, 2007
Hello,
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rod
View 10 Replies
View Related
Jun 16, 2015
I am trying to index dates to numbers with a large data set.
The first colums is index, the next is FactorsS, the next is value and the next is Date and the last is Lag.
Would it be difficult to write code that would determine the lag values. The lag value is based on the date value.
Index FactorS Value Date Lag
1 XYZ 2.3 12/31/2014 1
2 XYZ 1.4 12/30/2014 2
3 XYZ 3.3 12/29/2014 3
4 ABC 1.8 12/31/2014 1
5 ABC 2.2 12/30/2014 2
6 CBA 1.7 12/31/2014 1
7 CBA 1.8 12/30/2014 2
8 CBA 1.9 12/29/2014 3
9 CBA 2.1 12/28/2014 4
View 9 Replies
View Related
Jul 13, 2007
I work with a large and complex reporting system with several hundred reports: the Programmability; Stored Procedures node of the object explorer has become very difficult to navigate.
Is there any metadata that can be embedded in the stored procs that would create subfolders like the existing System Stored Procedures node in this node of the object explorer?
I suspect that the correct answers are:
Rename all your queries with a rational naming convention;
Cull the deadwood;
Assign them to categories then export them to separate databases.
Unfortunately, one of these has already been done, and the other two will break several hundred dependent processes - the recoding and retesting is neither economical nor desirable.
Still, all advice is welcome. Pitch your answers at a banking geek who does intermediate to advanced stored procedures and triggers, but isn't allowed to play with sharp things (like sys objects) - but I can probably get help from a grown-up on the sysadmin team: I have discovered that the rumours about human sacrifice are baseless, and they will perform favours in return for beer.
This is also a good time to ask: just how many stored procs and functions are you allowed in SQL Server 2005?
Nile.
View 5 Replies
View Related
Jun 20, 2007
I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.
Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);
this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;
}
}
protected void btnViewRpt_Click(object sender, EventArgs e)
{
ReportParameter[] rptParams = GetReportParameters();
this.rptViewer.ServerReport.SetParameters(rptParams);
this.rptViewer.ServerReport.Refresh();
}
private ReportParameter[] GetReportParameters()
{
int paramCount = 0;
ReportParameter[] retVal;
string emptyVal = null;
if (txtName.Text != "") paramCount++;
if (txtAddress.Text != "") paramCount++;
if (txtZip.Text != "") paramCount++;
retVal = new ReportParameter[paramCount];
paramCount = 0;
if (txtName.Text != "")
retVal[paramCount++] = new ReportParameter("Name", txtName.Text);
if (txtAddress.Text != "")
retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);
if (txtZip.Text != "")
retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);
return retVal;
}
The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.
The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.
So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.
Any suggestions?
View 7 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Jan 10, 2008
Hello!
Is there an easy way to deal with this situation below when reading in data from a SQL Database:
int? myNullableColumn;myNullableColumn = Convert.ToInt32(datarow["datacolumn"]);
Where, ideally, 'myNullableColumn' would be 'null' if the value was 'DBNull.Value'. This does not work because Convert.ToInt32 will not convert 'DBNull.Value to null', but instead throws an error.
Is there a built in funtion that does do this?
Thanks!
View 8 Replies
View Related
Mar 14, 2008
Ok, I have a table with about 47000 records in it. I have the following query for that table:Select ReportType =
Case
When ReportType = 1 Then 'Uniquery Report'
When ReportType = 2 Then 'SABRE Report'
When ReportType = 3 Then 'Menu Report'
Else Null
End,
ReportNameTo_,
Frequency.Frequency as Frequency,
ReportDate,
ReportDescription
From Report
Inner Join Frequency on ( Report.ReportFrequency = Frequency.FID )
Where ( Active = 1 )
And ReportDate = ( Select Max ( ReportDate ) From Report Where ( Active = 1 ) )
And ReportID = ( Select Max ( ReportID ) From Report Where ( Active = 1 ) )
The idea is that i need to get only the last report based off of unique reportname. I added a computer column to the table to give me the ReportNameTo_, since my deliminator is the _. Now my issue is that I have 1 records showing (the last record added to the table), which is right for the query that is written, but wrong for what I want. I need to only return the last record for each unique ReportNameTo_. So as an example, my table has the following ID, ReportNameTo_, Date fields the data looks something like this:
1, 123_, 1/1/20082, 123_, 1/1/20083, 124_, 1/1/20084, 124_, 1/1/20085, 125_, 1/1/20086, 125_, 1/1/20087, 126_, 1/1/20088, 126_, 1/1/2008
I only want to return the following:
2, 123_, 1/1/20084, 124_, 1/1/20086, 125_, 1/1/20088, 126_, 1/1/2008
Hope someone out there can let me know how to do this... I am almost there, just not all the way.
View 2 Replies
View Related
Feb 15, 2002
Hello!
Just looking for advise on dealing with duplicates in database.
I have a contact table that have a bunch of duplicated customer records.
My goal is to combine all duplicated records into one record.
This involves couple tables:contact,contact history ,calendar.
All tables related by common column "accountno".
What would be the best approach for this?
Thank you,
Lena
View 2 Replies
View Related
Mar 15, 2002
I have the following query in a stored procedure. If there are no rows in the history file, it returns a null. If there some setting or function that would have it return a zero if no rows are found? I use the variable to do arithmetic later on and a null messes everything up.
select@MarketTotal = sum(isnull(MarketValue,0))
fromhistory
whereEmpUID = @EmpUID and
Shares > 0
Thanks,
Ken Nicholson
View 2 Replies
View Related
Jun 9, 2006
Hi i have the following :
select agent, name, surname, address, cust1_text01, cust1_text02, phone1,
case call_type_id
*when NULL then ''
else call_type_id
end as 'call_type_id'
from Record_T
* I have also tried when NULL then space(1)
yet the query still returns NULL when this field is empty ?
the idea is to always return data, even if the field is NULL to
replace it with an empty space or spaces.
View 3 Replies
View Related
Sep 28, 2007
Hello,
Question 1:
In my senario i've developed a system which utilizes 2 database, i've writen queries like db1.dbo.table1 join db2.dbo.table2 etc... Now that db2 is getting huge, client wants to shift it to another server.
I don't know how to modifiy my queries to cope with such situation. Could somebody plz tell me on how to you write queries involving two databases from different servers.
Question 2:
I'm maintaining second database (db2) to keep track of records of db1 which have been processed by my software, so that when db1 gets added with more records i can compare db2 table with db1 table to identify which records are new.
db1 is not my database and i don't have any control over that, (it's some erp db), is there any way of identifying which rows have been processed. Can the need for db2 be eliminated?
I'm using SQL Server 2005
Thank You,
Sandeep.
View 1 Replies
View Related
Jul 31, 2006
hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks
View 1 Replies
View Related
Apr 4, 2007
I am trying to make a stored procedure in SQLServer Express.The question is related to this stored procedure / transact - sql. I think i am doing something wrong with datetime.Here is the stored procedure.The error i am getting is that:Msg 241, Level 16, State 1, Line 20Syntax error converting datetime from character string. ===================================== DECLARE @websiteID intDECLARE @dateFrom datetimeDECLARE @dateTo datetimeDECLARE @sortbystring varchar (20)set @websiteID = 1set @dateFrom = Convert(datetime, '2007-02-07 12:01:00')set @dateTo = Convert(datetime, '2007-03-07 11:59:00')set @sortbystring = 'Campaign'IF ISNULL(@dateTo, '') = ''begin SET @dateTo = @dateFromendSET @dateTo = DATEADD(d, 1, @dateTo)DECLARE @str CHAR(400)LINE 20: SET @str = 'SELECT dateEntry, c.name as Campaign, e.firstname as FirstName FROM entry e, campaign c WHERE e.campaignID = c.id ' + 'AND c.websiteID = @websiteID' + 'AND (ISNULL(' + @dateFrom + ', '''') = '''' OR e.dateEntry BETWEEN '' + @dateFrom + '' AND '' + @dateTo + '') ' + 'AND e.IP NOT IN (SELECT IP FROM IP) ' + ' ORDER BY dateEntry DESC'print (@str)===============================================
View 9 Replies
View Related
Dec 16, 2004
I have a stored procedure that takes less than 1 second in sql query analyzer to return my results.
I run this same SP in ASP.NET using a calendar control and using perf monitor I notice that for me from my dev machine my cpu utilization is sometimes over 40%.Is there any tweaks I can do to help decrease CPU utilization.
View 2 Replies
View Related
Jan 23, 2004
I'm looking for opinions here:
I have a stored procedure that has one required variable, and two optional variables, like this:
CREATE PROCEDURE sp_tariff_rule
@tariff_id INT,
@start_date DATETIME = NULL,
@end_date DATETIME = NULL
...etc...
I want the procedure to process
1) all data is no dates are presented
2) all data after the start date, if no end date is supplied
3) all data before the end date if no start date is supplied
4) all data between the start and end dates if both are supplied
Now, instead of an elaborate conditional, I added this to the WHERE clause of my SQL statement:
AND ((@start_date IS NULL OR service_date >= @start_date) AND (@end_date IS NULL OR service_date <= @end_date))
It works fine, but I want to know if anyone has a different/better way of doing it, or if there is a big bug waiting to happen here.
I typically don't like to create multipurpose routines in my code, but this is a better approach for my in a non-object-oriented world of SQL.
View 1 Replies
View Related
Nov 14, 2005
In SS 2000 it seems that there is no variable data type that can hold more than 8000 characters (varchar) or 4000 unicode characters (nvarchar). I've seen posts where multiple variables are spliced together to extend this limit. I am looking at performing string manipulations in an sproc and I need to be able to deal with the full 2GB/1GB limit of text and ntext field types. Is this possible? How do you deal with that?
View 14 Replies
View Related
May 31, 2007
Hello all. Got bit of a long winded question here...........so here we go lol.
OK.......ive got data on an Excel spreadsheet. Ive set the spreadsheet up as a linked server and i'm creating a set of insert statements from it by using the following code:
SELECT 'INSERT INTO TRAINREC (COURSE_NAME) VALUES ('''+
EMPLOY_REF + ''', '''+
FROM AtriumDD...['Employee Training Records$']
For most records this generates a correct insert statement.........for example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('153', 'NMA Panel');
However.........my problems start when the value for course name is containes an ' character. If it does the insert statement generated is incorrect. For example:
INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('139', 'Annual Accounting in Lloyd's Market');
can anyone suggest any ideas on how to get round this? Also if i havent explained it clearly enough just let me know and i can try and expand on it.
Thanks for reading.
View 5 Replies
View Related
Jul 20, 2005
I have a stored procedure as a recordsource from a contacts table. Inthis example, users can enter parameters to limit contacts by firstletter of last name or company name or keywords:Example:@myName nvarchar(30) = null,@Alpha char(1) = nullSELECTContacts.ContactID, ContactType,CASE WHEN Contacts.ContactType = 0THEN Contacts.CompanyNameELSEisNull(Contacts.LastName,'?') + ', ' +isNull(Contacts.FirstName,'?')ENDAS CNAMEFROMContactsWHERE(Keywords Like '%' + @myKeyword + '%' OR @myKeyword is Null)So far, so good, but...The problem is I want to also give the user the option of filteringalphabetically by first letter. I can't figure out how to deal withnulls in this example (user doesn't enter anything as parameter@Alpha):AND(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1) ENDOR@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyName,1) END)Any help is appreciated,LQ
View 2 Replies
View Related
Apr 3, 2007
Hi,
I just realized something. In the old DTS package I am migrating, there is an ActiveX script that checks for a certain condition in a row. If the condition is true, then it does:
DTSTransformationStat_SkipRow
I just can't believe there isn't an equivalent functionality in SSIS.
However, so far, I have tried the following:
1.) Redirect file error output (on all columns in the file)
2.) Use a conditional transform to search for a text string in a column (the "bad" row has different text in it)
And still, I keep getting errors that there is an "impartial row" in the file. Yes, I know that - why doesn't the error redirection catch this? Why doesn't the conditional expression catch it either?
Am I missing something here? Is it just buggy? I find it hard to believe I have to work around something that worked just fine in DTS.
Thanks
View 26 Replies
View Related
Apr 20, 2007
What methods work for storing empty dates? I've read that some people pick an old date and use it to represent empty. I'm not fond of the idea, because then I'll have to strip that date whenever I display the field in my UI.
Any other ways to do this? I'm using SQL Server 2005 and C#.
Thanks!
View 4 Replies
View Related
Jul 17, 2007
hi,
I'm building a C# database application that access a remote sql 2005 database. For the moment I am using sql express edition. My application will be running in several REMOTE camps which only have an internet connection via sattelite. The sattelite connection has a very high latency. I am wondering what workarounds or solutions are available for this situation. All applications need to access the same database and preferebly be notified when changes take place on the database.
Thanks in advance.
View 1 Replies
View Related
Apr 2, 2007
Hi EveryoneVery new to .net and currently dipping my toes in the water with a small application, but getting to the point -
I have a form which has somel text fields that expect a date but which are not a required field so in other words the user can leave them blank.
The code behind page stores the information using a stored procedure which I add parameters to in the following fashion -
SqlParameter userdate = new SqlParameter();userdate .ParameterName = "@dtdate";userdate .SqlDbType = SqlDbType.DateTime;userdate .Direction = ParameterDirection.Input;userdate .Value = dtdate.Text.ToString();cmd.Parameters.Add(userdate);
Now if I leave the text field dtdate blank I receive an error because the above expects a date.
If I remove the line userdate .SqlDbType = SqlDbType.DateTime; I don't recieve an error but my stored procedure saves the date as 01/01/1900 or similar.
I believe this is because in my stored procedure the paramger dtdate is defined as @dtdate datetime
Obviously I want to have it so that if the user leaves the text field empty then no date is saved in the database and was wondering how other people tackle this scenario.
View 3 Replies
View Related
