Any Idea What The Purpose Of This SPROC Might Be? (no Prizes, Sorry!)
Jan 19, 2007
I've been asked to document an application and I'm going through all
the Stored Procedures and trying to work out what they're supposed to
do.
Can anyone give me an idea of what the Stored Procedure
wsBookingListsGetAll below is trying to achieve? Is it incomplete? I
can't see any reason to pass in the Parameter, and what is the UNION
SELECT 0 all about?
Many thanks
Edward
CREATE Procedure wsBookingListsGetAll
@DebtorIDvarchar(15)
As
set nocount on
SELECT
fldBookingListID
FROM
tblWsBookingList
UNION
SELECT 0
return
GO
/* Table def */
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblWSBookingList]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblWSBookingList]
GO
CREATE TABLE [dbo].[tblWSBookingList] (
[fldDebtorID] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
,
[fldAddressCode] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldEmail] [varchar] (250) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldFirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingListID] [int] IDENTITY (1, 1) NOT NULL ,
[fldInvoiceNumber] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[fldPayeeID] [char] (15) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Wanted some candid feedback on this idea. Everyone knows that neural nets are a black box in terms of the weights and such it uses. The best you can do is to get an idea how "sensitive" the NN is to each input. I don't know of any example code that's out there to help you do this in an automated manner (correct me if I'm wrong) so I'm thinking about writing a sproc that would help with this task.
Basically, the sproc would take in the mining model's name and the key of one case. Let's say the case and its attributes are:
ZipCode=93901 (the case key)
MedianIncome=99,098
PopulationDensity=1234
AvgTemperature=74.3
Predict likelihood to respond to an offer
For that case, it would go through an iteration per input. First it would test the first input, MedianIncome. It would run 100 predictions using the input values listed above. All the inputs would remain the same throughout except for MedianIncome where it would try out the complete range of that input. Based upon how much the prediction changes, you would have an idea how sensitive the model is to MedianIncome. Then it would move on to the next input and do the same.
When it's done testing each input, it could spit out a dataset listing all the inputs in order of how sensitive the model is to them and a few other stats like min and max prediction.
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control..... No rows affected. (0 row(s) returned) No rows affected. (0 row(s) returned) Running [dbo].[sp_UPD_MESample_ACT_Formdata] ( @ME_Rev_Nbr = 570858 , @A1 = No , @A2 = No , @A5 = NA , @A6 = NA , @A7 = NA , @SectionA_Comments = none , @B1 = No , @B2 = Yes , @B3 = NA , @B4 = NA , @B5 = Yes , @B6 = No , @B7 = Yes , @SectionB_Comments = none , @EI_1 = N/A , @EI_2 = N/A , @UI_1 = N/A , @UI_2 = N/A , @HH_1 = N/A , @HH_2 = N/A , @SHEL_1 = 363-030 , @SHEL_2 = N/A , @SUA_1 = N/A, @SUA_2 = N/A , @Cert_Period = 10/1/06 - 12/31/06 , @CR_Rev_Completed = Y ).
No rows affected. (0 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[sp_UPD_MESample_ACT_Formdata]. The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0). And yet every time I try to update the record in the formview online... I get Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see. Does anyone have any tips or tricks or info that might help me?
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks, Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry ( @EntryLogID int, @ProjectID int, @NewCategoryID int, @Expended decimal(10,2) ) AS DECLARE@OldCategoryID int
********************************************* BP_GetLedgerCategory ********************************************* ALTER PROCEDURE BP_GetLedgerCategory ( @EntryLogID int ) AS
SELECT CategoryID FROM BP_EntryLog WHERE EntryLogID = @EntryLogID
RETURN
********************************************* BP_UpdateCategories ********************************************* ALTER PROCEDURE BP_UpdateCategories ( @ProjectID int, @NewCategoryID int, @Expended decimal(10,2), @OldCategoryID int ) AS
UPDATE BP_Categories SET CatExpended = CatExpended + @Expended WHERE ProjectID = @ProjectID AND CategoryID = @NewCategoryID
UPDATE BP_Categories SET CatExpended = CatExpended - @Expended WHERE ProjectID = @ProjectID AND CategoryID = @OldCategoryID
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6)) as DECLARE @CityID integer declare @StateID integer declare @ZipID integer set @ZipID=2 set @Zip5=lTrim(@Zip5) if @Zip5<>'' SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5) if @ZipID is null set @CityID= EXEC GetCityID(@City); set @StateID= EXEC GetStateID(@State); insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID) if @@ERROR = 0 SET @ZIPID = @@Identity select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs in the above stored proc? I mean what is the syntax??
May I know what is the purpose of having SqlDataReader in Example A? I can see the same output when I tried out both. Should I use Example A or Example B? Currently, I'm using Example B since it is lesser code.Example A Dim objDR As SqlDataReader 'Create Data Reader
LoginConn.Open() strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName " cmd = New SqlCommand(strSQL, LoginConn) objDR = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataSource = objDR ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close() Example BLoginConn.Open() strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName " cmd = New SqlCommand(strSQL, LoginConn) ddlNationality.DataSource = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close()
Can you really use the SQL replication to mirror data to another server for the purposr of DR ? What is the best method ? I would want to be able to switch to DR very quickly, allowing users to continue work. Or is there something I should know ? I would be very grateful for any help. :D
From a research paper I got to know that in transformation of one to many relation ship, in the "many type we have collection atribute consists of attribuet key from the one type. If we have a single key we will use a collection of simple type. Otherwise, we will have a collection of ROW type.
My probelm here is,is there any conditions to be cecked before using ROW type?Can't we use MULTISET instead of ROW type alone?
What is the purpose of the "N" preceding the parameter values in theSQL examples?Here is an example copied from Books Online, SP_ATTACH_DB:EXEC sp_attach_db @dbname = N'pubs',@filename1 = N'c:Program FilesMicrosoft SQLServerMSSQLDatapubs.mdf',@filename2 = N'c:Program FilesMicrosoft SQLServerMSSQLDatapubs_log.ldf'I've found that my sp_attach_db routine works without the "N", but Ineed to know what it is that I don't know.Thank you everybody for all your help.
In Chapter 7 of Microsoft Press "Inside Microsoft SQL Server 2005: T-SQL Querying" about use of TOP and APPLY, the authors use "TOP(0E0) PERCENT to TOP(100E0) ". What is the purpose of this 0E0, 100E0 notation?
Greetings,What is the point of Microsoft defining a ROWGUIDCOL property that canbe attached to a 'uniqueidentifier' column? This is defined as a columnthat is 'globally unique', but doesn't the uniqueidentifier datatypealready guarantee that? To make matters more confusing, they tell youin Books Online to add a Unique constraint because ROWGUIDCOL does notguarantee uniqueness...so what's the point?Apparently the only functionality attached to this property is that onlyone such column can exist per table and that it can be queried using the$ROWGUIDCOL keyword in SQL.Can anyone tell me the rationale for when to use this and when not to,or what the purpose of this property is?Thanks,Sam BendayanDB ArchitectUltimate SoftwareJoin Bytes!*** Sent via Developersdex http://www.developersdex.com ***
HI, we have a database that has a user created without login. I was wondering why using a user without login? Is there a reason for this? Can we use this user and how since it is not mapped to any login?
hi Experts,what is the purpose writing a date in the following format:where x.event_date >= {ts '1980-01-01 00:00:00'}versus like this:where x.event_date >= '1980-01-01 00:00:00'what benifit does it add to later form of writing?Thanks in advance.schal.
hi,I have data like 0, null or some value in one column. I want to usethis column for devision of some other column data.It gives me devision by 0 error.how can I replace all 0 and null with 1 in fly.thanks in adv.t.s.negi
.rdl files are critical and should be recorded in source control .rdl.data is useful only in preview (saving trips) and therefore should not be recorded in source control .rptproj file has useful datasource, path and deployment info and should be recorded in source control .sln contains useful solution container info and should be recorded in source control .suo contains user specific setting/preference info and should not be recorded in source control .rds contains important data source info and should be recorded in source control
...but cannot find info on the purpose of rptproj.user file and therefore dont know if recording such files in source control is useful.
Below is the many to one currency conversion MDX query generated by BI wizard. I am having hard time understanding the purpose of [[Reporting Currency].[Local]. Can anyone explain, why do we need [Reporting Currency].[Local], how does it work in the conversion and where are the relationships established between [Reporting Currency].[Local] member and other measures/dimensions? Thanks.
// This is the Many to One section
// All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension
I have a field called URL in my table. I want to get the SEARCH TERM from a given URL and create a report based on that information. I'm getting difficulties, because the URL have different format depending up on the search engine that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.
Examples of the URLs from google :
http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables
Does anybody have a sql query or used a CLR functions to get the SEARCH TERM from different search engine (URL).
I have a update trigger on a table which fires trapping the userid and time in the same record. The code is below. The tirgger only works if I have the recursive triggers option unchecked on databae properties. Is the way I am trying the only way to accomplish this in the update trigger or is there an more efficient way. Thanks
CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as -- updates record with sql user and timestamp --created 11-28-00 tim cronin DECLARE @muser varchar(35), @rec_lock_status int, @ptacpt_status int set @muser = current_user begin UPDATE PATIENT_MEDICATION_DISPERSAL_ set MODIFIED_BY = @muser, MODIFIED_TS = getdate() from deleted dt WHERE PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID end GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
Does anybody know what the "NT AUTHORITYSYSTEM" login create during a SQL Server 2005 instillation is used for?
Does this login pose a security risk, and can it be removed safely? It seems to me as if it is similar to the "BultinAdministrator" login which we remove from our production servers?
This might be an ignorant question, but I can't figure this out.
What is the purpose of the Error output data flow from the OLEDB Source? I am trying to understand an example of what kind of "error" would cause a row to go down this path, and I can't come up with one.
Does anyone have a good example of how this could be useful?
basically, we have two databases, one with fix name, eg. DB1 in all environments, and another is named with prefix based on environment, eg. DB2_DEV, DB2_TEST, that is generated by some managed application.
Then we have queries or view/SPs residing in the first database, eg. DB1 that access database resources from the second database, DB2_***. Both databases are residing in the same SQL instance.
Currently, we have hardcoded the database name in the query but would prefer not to manually rename or write other scripts to update the query with the correct database name when deploying to other environments.
For example:
SELECT * FROM DB2_DEV.dbo.vAccounts When deploy to TEST, we need to update the query to: SELECT * FROM DB2_TEST.dbo.vAccounts
So, I looked at Synonyms but it seems it can only create 'alias' for tables or views, and not database.
Hence, last thought is to create self linked server, so I can write my query like below and setup the linked server accordingly.
SELECT * FROM SRCDB.dbo.vAccounts
Any consideration to think of, eg. performance, security, etc?
i have a table FORUM_REPLY it contain follwing fieldsquest_id,answer_id, reply_user_id.i want who is post most answer.so i need reply_user_id and max(no_of_answer).but following query given reply_user_id and no_of_answer onlySELECT reply_user_id,count(answer_id) as no_of_answer FROM FORUM_REPLY GROUP BY(reply_user_id)How to get max_no_of_answer?
I am new to the DTS game - or at least to trying to do anything other than transfer rows between databases with identical structures.
I now need to create a far more complex DTS package to transfer data between an SQL Server database (I am using SQL Server 7.0) and an Oracle database. I have no trouble in making the connections and performing simple DTS's beween the two.
However, my current task is more complicated.
Problem 1: In one database I would store fifty values as 5 five records each containing 10 values (i.e. has ten fields). However, in the other database these fifty values would all be stored as one record (i.e. one row with 50 fields). How could I go about creating a DTS to transfer this information?
Problem 2: These values should only be transfered if certain conditions are met in an another unrelated table. For example, a flag in another table indicating that transfer of the said values should occur.
Any help with either of these problems would be much appraciated.
When one of the users log into the database via a thrid party application they receive an error message:
2000/11/16 10:40:13.84spid51Error : 605, Severity: 21, State: 1 2000/11/16 10:40:13.84spid51Attempt to fetch logical page 7832 in database 'highview' belongs to object '1241055457', not to object 'application_data'. 2000/11/16 10:45:07.68spid46Getpage: bstat=0x1008/0, sstat=0x80002110, disk 2000/11/16 10:45:07.68spid46pageno is/should be:objid is/should be: 2000/11/16 10:45:07.68spid460x1e98(7832)0x49f900e1(1241055457) 2000/11/16 10:45:07.68spid460x1e98(7832)0x4810b86f(1209055343) 2000/11/16 10:45:07.68spid46... extent objid 0, mask 0/0, next/prev=0/0 2000/11/16 10:45:07.68spid46... retry bufget after purging bp 0x2da7e060
I tried running DBCC checkdb, newalloc,& checktable to fix the probelm and than droping the table and rebuilding it but not success, any suggestion are more than welcome.
