Incorporating More Fields/more Options In SP

Dec 12, 2007

I would like to add a couple more fields that the users can query by, more options how would I add in the same stored proecedure, First Name, Date, and IR#?? so they can have the ability to run it by one field or two fields or all the above?? how would I incorporate those in the stored procedure??






Code Block
ALTER PROCEDURE [dbo].[AdvSearchRevocations]
( @Enter_LastName nvarchar(25) )
AS

IF patindex( '*', @Enter_LastName ) > 0
BEGIN
SET @Enter_LastName = replace( @Enter_Lastname, '*', '%' )

SELECT
LastName,
FirstName,
ReasonOfRevocation,
TM#,
[I/R #],
Date
FROM dbo.Revocations_Tbl
WHERE LastName LIKE @Enter_LastName
END

ELSE
BEGIN
IF patindex( '*', @Enter_LastName ) > 0
SET @Enter_LastName = replace( @Enter_Lastname, '*', '%' )
SELECT LastName,FirstName, ReasonOfRevocation, TM#, [I/R #], Date
FROM dbo.Revocations_Tbl
WHERE LastName LIKE @Enter_LastName

END
GO









is this correct





Code Block
CREATE PROCEDURE [SearchByDateExcls]
(@StartDateServed datetime,
@EndDateServed datetime, @Enter_LastName nvarchar(25), @Enter_Duration nvarchar(10))
AS SELECT IR#, [Date Served], [Reason For Exclusion], Duration, [First Name], [Last Name]
FROM [dbo].[Extended Exclusions]
GROUP BY IR#, [Date Served], [Reason For Exclusion] , Duration, [First Name], [Last Name]
HAVING (Date BETWEEN @StartDateServed AND @EndDateServed, Enter_LastName, @Enter_Duration)

View 3 Replies


ADVERTISEMENT

Incorporating Data Mining Wizard Into A Program?

Oct 21, 2006

Is there any way to incorporate the SQL 2005 Mining Wizard into my program? Sure, I can create my own mining wizard using DMX, but that's quite a bit of work. Has anyone tried this? Is it possible? Any pointers?

-Young K

View 4 Replies View Related

Incorporating Wildcards In A Report So Users Can Search Comments

Apr 15, 2014

I need to create a stored procedure and incorporate it into a report where the users can look up certain values in comment fields. This is what I have so far but I am getting errors.

Code:

CREATE PROCEDURE
[dbo].[SearchAccomplishments]
@Enter_Accomplishments text =null,
@Beginning_CompletionDate datetime = null,
@End_CompletionDate datetime = null
AS
IF patindex( '*', @Enter_Accomplishments ) > 0

[Code]...

View 1 Replies View Related

Time Series, Incorporating Future Input Data For Prediction

Sep 25, 2006

Hi,

I've got a time series of the number of new customer subscriptions, which
is the target attribute to predict. The number of subscriptions depends on
various marketing activities, such as mailings, which are known within
the time series for the past.


If I train an ART (MS Autoregression Tree), it learns the trend pattern
as well as the correlations between the marketing activities and the
target (cross-correlations), right?
What I would like to do is, providing the model with some marketing activity
planning for the future and let the model predict the number of new
subscriptions based on a) the past trend pattern and b) the future activities.


Unfortunately a time series algorithm does not provide some kind of scoring for input data.


What would be the best approach to solve this problem? How about linear regression?
But how to train a regression model with trend patterns?


Thanks for your help!

View 1 Replies View Related

Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.

Jan 28, 2008


I have just started using SQL Server reporting services and am stuck with creating subreports.

I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.

For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.

When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.

Am I missing something here? Any help is appreciated.

Thanks,
Sirisha

View 3 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Public Overridable ReadOnly Default Property Fields() As ADODB.Fields

Jan 26, 2008

sir

I have got this error message to establish connction with recordset vb .net, Can you please rectify this

Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'

my code like this


rs = New ADODB.Recordset

rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)


If txtUserName.Text = rs.Fields.Append(userid) Then


MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")

End If


thanks

View 1 Replies View Related

Update Fields With Searched First Date Record Fields

Jul 23, 2005

Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/

View 3 Replies View Related

Transact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?

Jul 2, 2015

I am using MS SQL 2012.  I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it.  Then I need a total of those added decimal fields. My data is like this:

I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:

ID, provider_name, uniq_id, total_spent, total_earned

Here is sample data:

1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00

I need the results to be just 3 lines:

Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00

But then I would need the totals for the Provider, so:

Harbor, 1800.00, 3800.00

View 3 Replies View Related

Search Multipe Fields, Compounding Fields, Like, Contains...?

Jul 20, 2005

I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M

View 1 Replies View Related

Update Fields With Data From Other Fields In Same Row

Jun 30, 2000

Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.

In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)

I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.

View 2 Replies View Related

Using LIKE To Find Any Of Fields In One Table In Fields Of Another

Jul 31, 2013

I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.

View 5 Replies View Related

Drillthrough In Calculated Fields Enable When Drillthrough Option Is Disable In Original Fields, Is This A BUG?

Jan 21, 2008

Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.


Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.

But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link

Is this the standard and expected behavior? or its simply a BUG?

Have I done something wrong in my model? and in this case, How I can correct this?

regards.
Julio Diaz.

View 1 Replies View Related

How To Set Options ONCE?

Jul 16, 2003

Very often, when I generate SQL scripts for a table, I forget to go to Option tab to click the pk, default, index boxes. Is there a way to permernatly set the whole sql server generate sql scripts options ONCE?

thanks
David

View 2 Replies View Related

Too Many SET Options

Mar 10, 2006

HiI have created a Sql Script through Enterprise Manager for Drop acolumn. By default its creating lot of 'SET' commands. I doubt allthese SET options are required or not. Pls comment on this issueBEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLESET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONALTER TABLE EmployeeDROP COLUMN OrderDetails_IDGOCOMMITDil

View 2 Replies View Related

Options?

Jan 18, 2008

Hi ,

I could do with a couple of pointers to the best options to acheive my goal, I'm pretty close with the way I've done it, but I feel there is a more elegant solution out their so your help would be most appreciated.

The problem is finding the best way of moving some SQL Server 2000 changed data into sql server 2005. We are only interested in some tables in 2000 (and sometimes just subsets of those). Because there are quite a few tables and the we want to set up a schedule to run periodically, we chose SSIS. The main reason for this is to utilise a for each loop that pulls each tables name from a one column staging table of table names. (that way we can do more or less comparisons by simply adding and removing from the staging table) Also in this loop, using the table name as a variable, we run an exec sql task along the lines of 'SELECT * from varTable EXCEPT SELECT * from varTable_tracker' which gives us the difference beteween the two tables (where the tracker table is a copy of the data table which is sychronised at the end of the job run). So far so good. Now the tricky bit, EXCEPT only works under 2005, the tables are in 2000 so we ended up having a linked server in 2005 back to the 2000 table. Is there a way of acheiving the same result without involving the linked server - or is there a task (script?) we can run to verify the linked server is up before we excecute the job -we already run checks on Connection Managers to see if they are up but never tried linked servers? Lastly, will performance be an issue

Thanks

View 5 Replies View Related

DBCC Options

May 4, 2002

anyone know about the undocument options of DBCC?
which options are undomented?
i.e.
log
There are only a few options which are documented in SQL Online Books
thank in advance

View 1 Replies View Related

Licensing Options

Dec 13, 1999

Is it necessary to reinstall SqlSvr 6.5 in order to chance
from 'per server' to 'per seat' licensing ?

View 1 Replies View Related

What SQL Dbcc Options Should Be Run .....

Aug 2, 2001

SQL 6.5

What are the most critical "dbcc" options that should be run to insure the sanity of database and DBA alike? Do these have to be run in single user mode or can they be run while users are on the system?

Thank You.

View 1 Replies View Related

Full T-Log: Options?

Apr 29, 2007

An automatic monthly delete has recently grown from 15 to 20 million rows. It is now filling my 70GB T-Log completly. I don't have any space to expand the T-Log. Do I have any options other than reducing the number of rows in the delete?

View 2 Replies View Related

Out Of Options, Need Performance Help.

Jan 21, 2004

hey guys, i need your help please.
here is the scenario:

1. I need to return a data back to client (result set varies 20-10,000)
2. I only want to show 20 records at a time
3. To get info i need to display i need to join 10 tables

When there are small #s of records it works but when i get over 8000 then it becomes a problem:

1. The first version was:
Get all data using big query and return everything back to client and display only 20 at a time (not very proficient).
Takes around 15 seconds to view 20 records.

2. Inspired by 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/062899-1.shtml)
Use Stored Procedure w/ server side paging logic to get 20 records at the time. I had to pass every filter parameter and stuff. SP had to sort resultset and return only 20 records i need to display.
Takes around 5 seconds to view 20 records.

I still think it's slow, i know this is a very broad question but is there any other way to do it, logically?

Any help is appreciated.
Wojo.

View 14 Replies View Related

SQL Server Options

Apr 10, 2004

Hi folks, Recently i've installed a fresh Installation of Opertaing Sys and SQL.
Win 2000 server, sp4
SQl 2000 Enterprise, sp3.
I am using Domain user service startup for sql and is member of domain admin. I've manually added the user in Local-Admin group of the system. Obviously it's also has Sys-Admin server roles.
The problem is; when i use enterprise manager and change any of the server settings; Priority Boost or Fix Memory Allocation for the Sql server, nothing happens, the options dialog box closes and doesn't ask for restarting the server neither does the settings take effect when i restart SQL.
However if i change the settings using sp_configure using the same user; it works. i've assigned a fixed memory to SQL but the option "Reserver Physical Memory For SQL" won't work. Couldn't find this option in SP_Configure.
Any ideas, what has gone wrong.

View 6 Replies View Related

Table Options

Aug 14, 2007

Hi all,

This follows on from a query I had a few days back (and for which I was promptly flamed! However, I've got skin like a rhinoseros, so here goes...)

I have a table - ProjectSite - that is pulling information from a two tables (Project and Site). This table contains data regarding which sites are part of which project.

I now want a means of reporting dates against this. The problem is that each project has bespoke milestone dates, so I can't just create columns in ProjectSite. The only solution I can see is to pull each project (and there's quite a few and its corresponding sites into a new table) and then I can create my bespoke columns.

Does this sound like the best viable option, or can anyone suggest another means of doing this?

TIA,

SamuelT

View 1 Replies View Related

Deployment Options

Aug 7, 2006

Hello all,

I am considering the different options for package deployment on the server.
Until now, I have found several different ways to deploy packages to the server (File System):


Using the Import option from the Management Studio (only one by one)
Using the Deployment Utility (Needs building the whole project. Opens all the packages in debugging mode, cannot deploy to different folders)
Using the dtutil by constructing a command line for each package deployment. (complicated)
Simply copying the files from the local project folder to the "Program FilesMicrosoft SQL Server90DTSPackages" folder on the server.

Does anyone have any other suggestions for deployment?
The 4th seems to be the easiest one, but I seen anybody suggesting such an action. What's the downside of such an action?

Thanks,
Liran

View 1 Replies View Related

RDA Tracking Options

Oct 4, 2007

I hoipe someone can point me in the right direction here.

I have an application with the following requirements (using SQL CE 2 alas)


A set of tables on the server that need to be imported to the handheld. Using rda, I need to get the modifications to these tables from the server (add/edit/delete) but the handheld will never update these tables.

A set of tables on the server that need to be imported to the handheld. The handheld needs to add/edit existing records, and it needs to get any changes from the server.

A set of tables on the server where the handheld needs to import a subset of the records. It needs to add (but not edit) new records, upload the new records to the server, and download any changed (add/edit/delete) records to the handheld.
What tracking options should I use in these 3 cases?

The problem comes in that I need to have some foreign key relationships in the database on the handheld. Since rda munges the names of primary keys (and indexes), I do not know of a good way to add these foreign key constraints. Any suggestions?

Thanks

View 2 Replies View Related

Reporting Options

Feb 2, 2007

Hello all. I am currently doing some research into options for
setting up reporting. Right now we have a server on EE that's
getting hit a bit too hard by the reports. The budget is
currently a bit low, but we already have a second server
purchased.



For our reporting, we need data that is up to date within the last 15
minutes (less if possible). Because of the potential size of some
of our transactions, I've ruled out log shipping as being too much
downtime of the reporting data while the second server is catching up
to speed. So, I'm trying to figure out what reporting options I
have left open to me.



1) I understand that for reporting purposes, a snapshot must be taken
of the mirrored server. Why are reports not able to run directly
off the mirror live (or am I mistaken?).



2) Is it possible to mirror from EE to SE (remember, low budget for the second server)?



3) How high is the overhead when doing a snapshot every 5-15 minutes (
I would think it's machine specific, but overall is it pretty quick or
prohibitive based on how often the snapshot would be needed)?



4) Is replication perhaps a better option based on how up to date the
data has to be? Are there any other options that may be available
for near-realtime reporting?



Thanks in advance for your time.

View 4 Replies View Related

Hosting Options

Jan 4, 2006

Hello,

 

I have been attempting to locate a hosting company that offers SQL Server 2005 in addition to Analysis & Reporting Services but have been unable to find a hosting company which does so without purchasing the entire server. Anyone know of a company?

 

Thanks in Advance,

Garrett

View 1 Replies View Related

Deployment Options Help!

Jul 5, 2006

Sorry I am a newbie in SSIS.

I have got a small project that requires to feed in a .CSV flat file and load the data into SQL server 2000.

I developed a SSIS package for this and get it working in my computer, but I need to deploy it to customers that they don't have VS 2005 or SSIS installed. May any one of you give me some clues on that?

I played around with the flat file deployment and again it seems only working on my computer as I have everything installed.

View 1 Replies View Related

Licensing Options

May 29, 2008

Hello all,

Just wanted to get some feedback on this scenario. I will be developing an ASP.net application for our local intranet that employees will have access to. It may also be implmented to allow employees to access this ASP site from home as well. I would be using SQL Server 2000 as the backend DB and wondered what type of licensing would best fit this scenario?


Sorry if this is double posted....seemed to be having some issues with posting.

Thank you!

View 6 Replies View Related

ArithAbort And ArithIgnore Options

Jul 26, 2006

Hi,
I was recently experiencing a slowness when executing stored procedures from a .NET Application, but it went fast when executing from Query Analyzer. Research led me to find that by turning ArithAbort ON that it forces the SQL Server to use the same Execution plan whether the request is coming from Query Analyzer or the Application.
My concern now is the effect of ArithAbort. I understand what turning this option does, but I am trying to think of a scenario where turning it on could be bad.  Does anyone have any suggestions on what I should be aware of when disabling/enabling ArithAbort or ArithIgnore?
Thanks.
-Brian

View 1 Replies View Related

Sqldatasource And Advanced Options

Mar 13, 2007

Hello, I am trying to bind a sqldatasource control to the gridview.
I have selected the sqldatasource control and specified the connection string, on configure  Select statement page under advanced options both the check marks
Generate INSERT, UPDATE and DELETE Statements
Use Optimistic Concurrency  
are disabled for me.I have a proper SQL Server database not an express data base, how do i get to generate the InserCommand, EditCommand etc
Any help would be great .. relatively new here
thanks 
 

View 2 Replies View Related

Multiple Options Into One SQL Field

Apr 13, 2007

Morning all,

I'm writing an Insert form which will write records to a few tables. What I want to know is how do I write multiple answers to one question in different rows in the table but keeping the ID?

For example.

The form has the following fields:

HotelIDHotelFacilities (CheckBoxList)

Now each hotel (in this case) will only have one ID but more than one HotelFacility .

How do I get my table to read...

 




HotelID

HotelFacility




1

Bar




1

Restaurant




1

Cafe




1

Wi-Fi Access


I presume INSERT INTO tblHotelFacilities(HotelID, HotelFacility)                  VALUES(@HotelID, @HotelFacility) won't write more than one selected facility?Thanks,Brett 

View 5 Replies View Related

Advanced SQL Generations Options

May 13, 2007

Advanced SQL generations options:
generate INSERT, UPDATE, and DELETE statements is all greyed out?
in my sql data source control.?
I have made a brand new instance with sql server management express....have I missed something?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved