How To Optimise This Code?

Apr 18, 2008

Hi,

I use this piece of code in a trigger to check if new entered row has unique order number in the given year:


IF EXISTS

(

SELECT COUNT(*)

FROM SPD_Orders

GROUP BY ORD_Year, ORD_Number

HAVING COUNT(*) <> 1

)

RAISERROR('Order with this No exists in given year, 11, 1)


but this code causes deadlocks in my database.

Any suggestions how can I change it to avoid these deadlock?
1. Some constrain? If yes, so how to write it?
2. Index like to enable query optimiser to change execution plan?

CREATE NONCLUSTERED INDEX [_dta_index_SPD_Orders_10_1429580131__K8_K7] ON [dbo].[SPD_Orders]

(

[ORD_Year] ASC,

[ORD_Number] ASC

3. Same index as above but unique and then I will not have to use this trigger, cause database itself with return an error?
4. Any other, the best solution ?

Please help.

Thanks.
Przemo

View 4 Replies


ADVERTISEMENT

Query Optimise

Jun 2, 2006

Hello All,
I have a query that I could use some help with. I would like to see how I could make this query more optimised. I am not hte best at Joins so if someone could help me with this that would be great. Right now its taking about 24-30 secs to run which is a no go. Im wondering if some kind of join would work better?
thank you much
-jes

SELECTrecipes_signed_up_for.user_id,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.use_in_lesson_plan AS use_in_lesson_plan,
recipes_signed_up_for.attached_lesson_plan AS attached_lesson_plan,
recipes_signed_up_for.participate,
recipes_signed_up_for.authorized AS authorized,
recipes_signed_up_for.date_signed_up_for AS date_signed_up_for,
users.user_id,
users.f_name AS f_name,
users.l_name AS l_name,
users.email_address AS email_address,
users.primary_phone AS primary_phone,
recipes.recipe_name AS recipe_name,
recipes.recipe_id AS recipe_id,
recipes.number_served AS number_served,
recipes.last_day_to_sign_up_for AS last_day_to_sign_up_for,
recipes.recipe_instructions AS recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.ingredient_id,
recipe_ingredients.ingredient_quantity AS ingredient_quantity,
recipe_ingredients.ingredient_unit,
recipe_ingredients.active_ingredient AS active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient AS ingredient_name,
recipes_signed_up_for_ingredients_needed.ingredient_id,
recipes_signed_up_for_ingredients_needed.ingredient_needed AS ingredient_needed
FROMrecipes, recipe_ingredients, ingredients, recipes_signed_up_for_ingredients_needed, recipes_signed_up_for, users, locations, regions
WHERErecipes.recipe_id = recipe_ingredients.recipe_id
AND recipe_ingredients.ingredient_id = ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.user_id = #url.user_id#
AND recipes_signed_up_for_ingredients_needed.ingredient_id = recipe_ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.ingredient_needed = 1
AND recipes.recipe_id = recipes_signed_up_for.recipe_id
AND recipes_signed_up_for.user_id = users.user_id
AND recipes_signed_up_for.user_id = #URL.user_id#
AND recipes_signed_up_for.participate = 1
AND locations.region_id = regions.region_id
AND recipes_signed_up_for.recipe_id IN (SELECTrecipe_id
FROMrecipes
WHEREactive_recipe = 1)
ORDER BY recipes.recipe_name

View 9 Replies View Related

Any Way To Optimise This Query?

Oct 11, 2005

Hi guysIs there any way I can run this query faster? Should I take out the ORDER BYclause? This is supposed to return 17,000 rows and takes around 30 minutes orso. Is there no way at all to get this result faster?select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.clientinitials, r.clientidno, r.grantedamount, r.bankname,r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,r.erfdescription, r.commenthistory, br.expectedregdatefrom bondtrak..rptdetail rjoin ebondprd..bankresponse bron br.applicationid = r.applicationidwherer.rundate = '20051010'and r.primarybankind = 'Y'and r.status = 'granted'and r.statusdate between '20020101' and '20050930'and r.businessunit in ('bond choice', 'ppl')order by r.sitename, r.consultantname, r.statusdateThanks for any help.Driesen--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1

View 6 Replies View Related

Optimise These 3 Queries Into One?

Jul 20, 2005

I have these 3 queries - they are the same except each fetches record countsfor one of 3 different record types, nSubsets (type 0), nAssets (type 1) andnImages (type 2). Is there any way I could get all 3 of these (based on theNode.Type integer) with a single query?IF @Error = 0BEGINSELECT @nSubsets = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 0SET @Error = @ERRORENDIF @Error = 0BEGINSELECT @nAssets = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 1SET @Error = @ERRORENDIF @Error = 0BEGINSELECT @nImages = COUNT(*)FROM NodeINNER JOIN AdjacencyON Adjacency.ID_Node = Node.IDWHERE Adjacency.Path LIKE @nodepath + '%'ANDNode.Type = 2SET @Error = @ERROREND

View 5 Replies View Related

How To Optimise This Query?

May 29, 2008

hi,
I want to know how to optimise this query, the results almost reach 6000 rows, I want to speed this query.


condition2=Select Distinct A,B,B+C+D,E,F,G From a.table where condition1 order by A,B,B+C+D,E,F,G
Select Sum(amount) From b.table where condition2 And date=datetime.

Thanks in advance.

View 1 Replies View Related

SubQuery In Both SELECT And WHERE Part. How To Optimise?

Apr 11, 2008

Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.SELECTf.FeedbackText AS [Feedback Comment],u.Name AS [Feedback Author],f.CreatedDate AS [Created On],(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) AS [Responses]FROM Feedback fINNER JOIN [User] u ON f.StaffID = u.StaffIDWHERE f.CreatedDate >= @DateFromAND f.CreatedDate <= @DateToAND(@HasResponses IS NULLOR(@HasResponses = 1 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) > 0)OR(@HasResponses = 0 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) = 0))

View 2 Replies View Related

Optimise Import Text File

Mar 11, 2008

Hi,

Please can you help me,

I have created an database with 3 fields

View 3 Replies View Related

Optimise Import Text File

Mar 11, 2008

Hi,

Please can you help me,

I have created an database with 3 indexed (not clustered) fields
key : bytes 100
error : integer
status : bytes 6
I would like to know how to import 3 million or more records quickly.
If I don't index the the fields, the operation takes 3 hours. With indexed fields it takes overnight. Therefore, how can i optomise the import operation from a .txt file.

Thanks and regards.

View 3 Replies View Related

Simple Methods To Optimise SQL Queries

Sep 18, 2006

Hi, please excuse me if this seems like a daft or badly formed question.
A colleage mentioned that when constructing the where clause in a query that it was important what order the criteria were entered. They weren't sure themsleves but the idea was that starting from the bottom of the query you should add the criteria that will have the effect of reducing the data the most and then work 'up' the query back to the from clause.
As a dodgy example, if you have a staff table and you want to run a specific query on all staff over 75 that are male, then:

where gender = "male"
and age >- 75
The reasoning being that the age criteria would reduce the size of the data being worked on more than the gender criteria.
It's been a while since I've had to look at intermediate tables etc but i'm pretty sure this suggestion will not make any difference to the performance of a query.
Thanks,

Matt

View 2 Replies View Related

Optimise Stored Procedure Respone Time?

Apr 30, 2008



Hello,
If i want to optimise stored procedure response time.
Which things i need to be considered?

Thanks in advance.

View 3 Replies View Related

How To &"Optimise&" An SQL 2000 Db?

Nov 14, 2006

Have built tables with stitistical data (very large)in SQL Server 2000

All with 2 fields in common: "Node" and "Datestamp"
At the moment none is indexed.

Tables will have joins added in Business Objects Universe.
How do I know if adding indicis would improve speed of queries?
And if so how are these best added?
Do all tables need indicis or just some?
What does it mean to Optimise the db above adding Index columns?
Can the DB be made optimise itself automatically?

Any prompts very appreciated,

Gezza

View 3 Replies View Related

Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005

Jul 27, 2006

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

View 7 Replies View Related

How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)

Mar 28, 2007

Dear Friends,



I am having 2 Tables.

Table 1: AddressBook
Fields --> User Name, Address, CountryCode



Table 2: Country
Fields --> Country Code, Country Name


Step 1 : I have created a Cube with these two tables using SSAS.



Step 2 : I have created a report in SSRS showing Address list.

The Column in the report are User Name, Address, Country Name



But I have no idea, how to convert this Country Code to Country name.

I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]



Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.




Thanks in advance.





Regards
Ramakrishnan
Singapore
28 March 2007

View 4 Replies View Related

Many Lines Of Code In Stored Procedure && Code Behind

Feb 24, 2008

Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you

View 2 Replies View Related

Custom Code (Embedded Code) Question

Oct 16, 2007



Hi all,

Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?

Also, can custom code function alter the parameters of the report, or refresh the report?

Thanks.

View 2 Replies View Related

Putting SqlDataSource Code In Code-behind

Jan 25, 2007

Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks  geniuses.  

View 2 Replies View Related

&&<Code&&>-8462&&</Code&&>

Apr 19, 2006

Hi:

My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.

After I receive the message successfully...in the end I get this message sent...

<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">

<Code>-8462</Code>

<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>

</Error>

Why am i gettting this error after the conversation.

Thanks,

Pramod

View 7 Replies View Related

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.

Jan 28, 2008

Hi All,

Recently in an SSIS package I am getting the following error for a particular Data flow task.





Error: 2008-01-25 12:01:48.58

Code: 0xC0202009

Source: Import Datasynapse Data User Events Source [3017]

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

End Error

Error: 2008-01-25 12:01:48.73

Code: 0xC004701A

Source: Import Datasynapse Data DTS.Pipeline

Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.

End Error

Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?

Since this is very urgent, immediate response would be very much appreciated.

Thanks & Regards,
Prakash Srinivasan

View 4 Replies View Related

Need T-sql Code

Mar 11, 2008

Right the problem is that i have an sql query that returns multiple rows and i want to be able to join all these rows into one so that i can use it.  so for exampleRows returnedRow1Row2Row3Rolled up rowsRow1 Row2 Row3
 
using sql query
please help me out

View 2 Replies View Related

Need Help With Code

Sep 7, 2000

Can anyone tell me what is wrong with this line of code..
I am missing someting on both line of code

exec ("print 'DBCC INPUTBUFFER FOR SPID " + @spid + "'")
exec ("dbcc inputbuffer (" + @spid + ")")

I am getting the following errors...

Server: Msg 170, Level 15, State 1, Procedure sp_blocker_pss70, Line 146
Line 146: Incorrect syntax near 'print 'DBCC INPUTBUFFER FOR SPID '.
Server: Msg 170, Level 15, State 1, Procedure sp_blocker_pss70, Line 147
Line 147: Incorrect syntax near 'dbcc inputbuffer ('.

View 2 Replies View Related

Code Help!!!!

Jun 15, 2000

I can not get this code to work, I dey try to convert a non relational database, catcde na the name of the field in the non conversional database and i have a table in the relational database that called DuesCategory, that it's DuesCategoryname has the following fields,
Retired
On Leave
No Dues
Agency Fee Payer
Student
Full Dues
3/4 Dues
1/2 Dues
1/4 Dues
1/8 Dues

the fields in the non relational are not d same that is why i am trying to map it using the case statements, and since i will be converting alot of databases i what it to be based on which peremeter they put in.
select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end

This is all the code
CREATE Procedure _RunIndividual_5 @tablename varchar(100) as
Exec ('declare cur_individual cursor for
SELECT last, first, mi, address1, address2, city, state, zip,
bdate, sex, soc, mstat, depnum, hometel, status, sefdte, catcde,
cefdate, poscd, poedte, rfdlt, duespthru, paydeduc, classcode,
location, deptcode, tenuresub, ocertsub, bassalary, salarystep,
salarycol, startdate, initdate, seniordate, worktel, votecope, votedate,
voteded, polparty, polactiv, precinct, condst, sendst, asmdst, rschd,
loccode, extra1, extra2, extra3, extra4, extra5, extra6, extra7,
extra8, extra9, extra10, access, pctype, os, pclocation, internet,
email, show, county, localname, localnum, bargin, offtitle2, offtitle,
regvoter, reshigh, reselem, resunit, rescollege, employer, empnum, workfax,
offtitle3, work_phone, const_code, class_titl, faxnumber, constit
FROM Conversion.dbo.'+ @tablename+' order by last,first')

----Declare variables
declare @last nvarchar (50) ,
@first nvarchar (50) ,
@mi nvarchar (50) ,
@address1 nvarchar (50) ,
@address2 nvarchar (50) ,
@city nvarchar (50) ,
@state nvarchar (50) ,
@zip nvarchar (255) ,
@bdate nvarchar (255) ,
@sex nvarchar (255) ,
@soc nvarchar (255) ,
@mstat nvarchar (255) ,
@depnum float ,
@hometel nvarchar (255) ,
@status nvarchar (255) ,
@sefdte nvarchar (255) ,
@catcde nvarchar (255) ,
@cefdate nvarchar (255) ,
@poscd nvarchar (50) ,
@poedte nvarchar (255) ,
@rfdlt nvarchar (255) ,
@duespthru nvarchar (255) ,
@paydeduc nvarchar (255) ,
@classcode nvarchar (50) ,
@location nvarchar (255) ,
@deptcode nvarchar (255) ,
@tenuresub nvarchar (255) ,
@ocertsub nvarchar (255) ,
@bassalary float ,
@salarystep float ,
@salarycol nvarchar (255) ,
@startdate nvarchar (255) ,
@initdate nvarchar (255) ,
@seniordate nvarchar (255) ,
@worktel nvarchar (255) ,
@votecope float ,
@votedate nvarchar (255) ,
@voteded nvarchar (255) ,
@polparty nvarchar (255) ,
@polactiv nvarchar (255) ,
@precinct nvarchar (255) ,
@condst nvarchar (255) ,
@sendst nvarchar (255) ,
@asmdst nvarchar (255) ,
@rschd nvarchar (255) ,
@loccode int ,
@extra1 nvarchar (255) ,
@extra2 nvarchar (255) ,
@extra3 nvarchar (255) ,
@extra4 nvarchar (255) ,
@extra5 nvarchar (255) ,
@extra6 nvarchar (255) ,
@extra7 nvarchar (255) ,
@extra8 nvarchar (255) ,
@extra9 nvarchar (255) ,
@extra10 nvarchar (255) ,
@access bit ,
@pctype nvarchar (255) ,
@os nvarchar (255) ,
@pclocation nvarchar (255) ,
@internet nvarchar (255) ,
@email nvarchar (255) ,
@show bit ,
@county nvarchar (255) ,
@localname nvarchar (255) ,
@localnum nvarchar (255) ,
@bargin nvarchar (255) ,
@offtitle2 nvarchar (255) ,
@offtitle nvarchar (255) ,
@regvoter nvarchar (255) ,
@reshigh nvarchar (255) ,
@reselem nvarchar (255) ,
@resunit nvarchar (255) ,
@rescollege nvarchar (255) ,
@employer nvarchar (255) ,
@empnum nvarchar (255) ,
@workfax nvarchar (255) ,
@offtitle3 nvarchar (255) ,
@work_phone nvarchar (255) ,
@const_code nvarchar (255) ,
@class_titl nvarchar (255) ,
@faxnumber nvarchar (255) ,
@constit nvarchar (255) ,
@DuesCategoryName nvarchar (255) ,
@DuesCategoryName1 nvarchar (255) ,
@DuesCategoryName2 nvarchar (255) ,
@DuesCategoryName3 nvarchar(255) ,
@DuesCategoryName4 nvarchar(255) ,
@DuesCategoryName5 nvarchar(255) ,
@DuesCategoryName6 nvarchar(255) ,
@DuesCategoryName7 nvarchar(255) ,
@DuesCategoryName8 nvarchar(255) ,
@DuesCategoryName9 nvarchar(255) ,
@DuesCategoryName10 nvarchar(255) ,
@DuesCategoryName11 nvarchar(255) ,
--- other variables
@prefix varchar(5),@prefixid uniqueidentifier,@worksiteid uniqueidentifier,
@chapterid uniqueidentifier,@PaymentMethodName varchar(50),@PaymentMethodID uniqueidentifier,
@DuesCategoryID uniqueidentifier,@DuesCategoryLocalID uniqueidentifier,
@DeactivateReasonID uniqueidentifier,@DeactivateReasonLocalID uniqueidentifier,
@JobClassID uniqueidentifier,@LocalJobClassID uniqueidentifier,
@MaritalStatusID uniqueidentifier,@MemberStatusID uniqueidentifier,
@SubjectID uniqueidentifier,@PoliticalPartyID uniqueidentifier,
@EmployerID uniqueidentifier, @LocalUnionID uniqueidentifier,@LocalUnionNbr char(5),
@addressID uniqueidentifier,@StateTerritoryId uniqueidentifier,
@CountryId uniqueidentifier,@PoliticallyActiveTF bit,@IndividualId uniqueidentifier


begin
open cur_individual
fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @prefix= case @sex when 'Male' then 'Mr.'
when 'Female' then 'Ms.'
when ' ' then null
end
select @prefixid=prefixid from prefix where prefixname=@prefix
select @worksiteid=worksiteid,@EmployerID=EmployerID from worksite where worksitenumber=@loccode
select @chapterid=chapterid from chapter where localunionid in (select localunionid from localunion)
select @PaymentMethodName= case @paydeduc when 'YES' then 'Payroll Deduction'
end
Select @PaymentMethodID=PaymentMethodID from PaymentMethod where PaymentMethodName=@PaymentMethodName

select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end
select @DeactivateReasonID=DeactivateReasonID,@Deactivate ReasonLocalID= DeactivateReasonLocalID
from DeactivateReasonLocal
where DeactivateReasonLocalName=@rfdlt
select @MaritalStatusID=MaritalStatusID from MaritalStatus where MaritalStatusName=@mstat
select @MemberStatusID=MemberStatusID from MemberStatus where MemberStatusName=@Status
select @SubjectID=SubjectID from Subject where SubjectName=@tenuresub
select @PoliticalPartyID=PoliticalPartyID from PoliticalParty where PoliticalPartyName=@polparty
select @LocalUnionID=LocalUnionID,@LocalUnionNbr=LocalUni onNbr from Localunion
select @StateTerritoryId=StateTerritoryId,@CountryId=Coun tryId from Address where StateCode=@State
Insert into Address values (newid(),@Address1,@Address2,@City,null,@State,@zi p,
null,null,'USA',@StateTerritoryId,@CountryId,NULL, 0,@county,NULL,0,default,default,
default,default,default,@LocalUnionNbr,@LocalUnion Id)
Select @AddressId=AddressId from Address where uid=@@Identity
select @LocalJobClassId=LocalJobClassId from LocalJobClass where LocalJobClassCode=@classcode

---- Inserting Individual rows

insert into individual values (
newid(), @soc, null, @last,@First, @mi,null,CONVERT(DATETIME,@bdate),@PrefixId,@sex,
null, 1, @MaritalStatusId,null,null,0,null,null,null, null,@WorksiteId,null,
@JobClassId,@LocalJobClassId, CONVERT(DATETIME,@STARTDATE), null, null, CONVERT(DATETIME,@seniordate), null, @location,@deptcode,
null,null, 0, @condst, @regvoter, null, @sendst, @rschd,@asmdst, @PolActiv,@PoliticalPartyId,
@precinct,null, null, 0, @SubjectId, null,default, 0,null, @Hometel,null, @Worktel,null,
@MemberStatusId, null,null, @Email,null,@ChapterId,@DuesCategoryId,
null, @AddressId, CONVERT(DATETIME,@CEFDATE),0 , NULL,@PaymentMethodId,@DUESPTHRU,
NULL,CONVERT(DATETIME,@InitDate), @DuesCategoryLocalId,null,@DeactivateReasonId,
null, null, @LocalUnionNbr, null, null, @LocalUnionId, null, null, 0, null,
null, @WorkFax, null, null, null, null, @DeactivateReasonLocalId, null,
null, null, null, null,null,null,null,null,null,null,null,null,null, null,
null,null,null,null, null,null,null,null,null,null,default,default,defa ult,default,
default,@EmployerId, null, null, null,null,null,null,null,null,default,default,
default,default,default,default,default,default)

select @IndividualId=IndividualId from Individual where LastName=@Last and FirstName=@first and
MiddleName=@mi and ssn=@soc and dob=@bdate and Gender=@sex
insert into COPE values (newid(),null,@IndividualId,null,@votecope,convert (datetime,@votedate),
default,default,default,default,default,@LocalUnio nNbr,@LocalUnionId)

Select @prefixId=null

fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
END
CLOSE CUR_individual
DEALLOCATE CUR_individual
END

update individual set homeaddressid=addressid where addressid is not null -- set default address as home


Thanks

View 2 Replies View Related

The Code Behind Sum().

Jun 7, 2006

Hi, I like to write a function similar to sum(), does anyone have the code for it? what I like to do is write a function that concatenate a group of strings those having the same group as defined by a user and return a string as varchar datatype.

Thanks,
Benjamin

View 2 Replies View Related

Code Help!

Nov 17, 2004

Is there a store procedure to determine if you have outlook on your server to send mail.

Lystra

View 2 Replies View Related

Help With Code

Dec 2, 2004

I have this code in a DTS package which is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

drop proc VerifyRequest
go
/*
* VerifyRequestTransfer - run a command that looks for @filename in the output
*

*
* This proc looks for a file matching 'tbl_%' in the output of an ftp command.
* The output message reports success/failure of transfer.
* A return code of 1 indicates success
* Return code = 0 indicates failure.
*
* How it Works:
* ftp is executed using @ftpcommandfile as input to the -s parameter.
* The output of ftp is written to a table
* The table is cleared of garbage records
* The count of records matching @filename is checked
* if the count = 1 then there success!
*/
CREATE proc VerifyRequest
@filename varchar(200),
@ftpcommandfile varchar(1000)
as

declare @rc int
declare @rows int, @errcode int, @rows2 int
set @rc = 0
set @rows = -9998

set nocount on
-- build a table containing list of files in Request directory
if exists (select * from tempdb.dbo.sysobjects where name='RequestFiles' and type = 'U')
drop table tempdb.dbo.RequestFiles
create table tempdb.dbo.RequestFiles (
line_no int identity(1,1) Primary key clustered,
Filename varchar(200) NULL
)
declare @cmd varchar(2000)

--Get list of remote files
set @cmd = 'ftp -i -s:' + @ftpcommandfile
Insert into tempdb.dbo.RequestFiles (Filename)
Exec master.dbo.xp_cmdshell @cmd
select @rows = @@rowcount, @errcode = @@error
if @rows = 0 OR @errcode != 0
begin
set @rc = -1
goto done
end

-- remove non-files and already processed files( there might have been old files on remotesystem )
Delete
From tempdb.dbo.RequestFiles
Where coalesce(Filename, '') not like '%tbl_%'
-- check count
select @rows = (select count(*) from tempdb.dbo.RequestFiles
Where tempdb.dbo.RequestFiles.Filename like '%'+@filename+'%' )

if @rows = 1
set @rc = 1
done:
return @rc
go

Now the message I am getting is:

The task reported failure on execution. Procedure 'VerifyRequest' expects Parameter '@filename', which was not supplied.

I don't know where to set this parameter.


I hope someone can help.

Thanks

Lystra

View 3 Replies View Related

SP Code

Sep 26, 2005

Hi,

The script below is just the beginning of something a bit more complicated, which I'm not sure how I'll explain so depending on the amount of posts I'll explain further.

For the time being can anybody see if there's anything wrong with how I've scripted the SP below particulary the loop (Check thread - Stored Procedure Programming)?

Thanks for your input in advance.

:)

CREATE PROCEDURE newimr_sp_duplicate_report
(
@countryid as INT,
@marketsectorid as INT, -- marketsector to transfer from
@specialreportid as INT
)
AS

Declare
@other_marketsectorid as INT

if (@marketsectorid = 1)
set @other_marketsectorid = 2
else
set @other_marketsectorid = 1

declare topicids cursor for
select id, description from tbl_topics where specialreportid = 7 and marketsectorid = @marketsectorid
open topicids
while 1 = 1
begin
declare
@other_topicid as int,
@description as varchar(255)
fetch next from topicids into @other_topicid, @description
if @@fetch_status <> 0
break

select @description

end

close topicids
deallocate topicids

GO

View 11 Replies View Related

T-SQL Code Help.

Mar 4, 2004

I'm fairly new to SQL

Should be easy for someone

Example dataset:

Table1

ID Code
1 A
1 B
2 A

Result set I want
ID Code
2 A

so I only want ID's that = A but don't also have B

I was trying intersects but T-SQL has issues with it.

View 3 Replies View Related

SQL Code Help!!

Jan 21, 2006

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '{'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

What does this mean?

This is what I am trying to do.

Which students are enrolled in Database (Introduction to Relational Databases) and Networking? (Hint: Use the SECTION_ID for each class so you can determine the answer from the IS_REGISTERED table by itself.)


Student_ID Student_Name
38214 Letersky
54907 Altvater
66324 Aiken
70542 Marra

STUDENT (Student_ID, Student_Name)




Faculty_ID Faculty_Name
2143 Birkin
3467 Berndt
4756 Collins

FACULTY (Faculty_ID, Faculty_Name)



Student_ID Section_ID Semester
38214 2714 I-2001
54907 2714 I-2001
54907 2715 I-2001
66324 2713 I-2001

IS_REGISTERED (Student_ID, Section_ID, Semester)


Course_ID Course_Name
ISM 3113 Syst Analysis
ISM 3112 Syst Design
ISM 4212 Database
ISM 4930 Networking

COURSE (Course_ID, Course_Name)





Section_ID Course_ID
2712 ISM 3113
2713 ISM 3113
2714 ISM 4212
2715 ISM 4930

SECTION (Section_ID, Course_ID)


Faculty_ID Course_ID Date_Qualified
2143 ISM 3112 9/1988
2143 ISM 3113 9/1988
3467 ISM 4212 9/1995
3467 ISM 4930 9/1996
4756 ISM 3113 9/1991
4756 ISM 3112 9/1991

IS_QUALIFIED (Faculty_ID, Course_ID, Date_Qualified)


This is what I did and got the error message at the top. What did I do wrong?

Select * FROM Student WHERE Student_ID IN{
Select Student_ID FROM Is_Registered WHERE Section_ID IN(
Select Section_ID FROM Section WHERE Course_ID IN(
Select Course_ID FROM Course WHERE Course_ID LIKE '964212')))
AND Student_ID IN(
Select Student_ID FROM Is_Registered WHERE Section_ID IN(
Select Section_ID FROM Section WHERE Course_ID IN(
Select Course_ID FROM Course WHERE Course_ID LIKE '964930')))





albanie

View 5 Replies View Related

How To Code This ?

May 14, 2007

Hi,

How can write this in a query:

If @maxLastUpdate is later than LastUpdate, call stored procedure usp_RubricReportUpdate @DataYears, @County, @Distirct to update the report.

View 3 Replies View Related

How To Code?

May 14, 2007

Hi,

How can I code this:

Set the values of GoalMet comparing the LocalPerf value and SppTarget.Target value with the CompareMethod. If CompareMethod = 1, then LocalPerf >= Target means GoalMet = 1; othwerwise, LocalPerf <= Target means GoalMet = 1.

Thanks in advance.

View 3 Replies View Related

Help With SQL-code

Oct 25, 2007

Hello everyone!

I need some help with my code. It says that (the blood marked) is wrong.
I want to enter a kund number and it will show his first and last name and what product he/she has ordered.


SELECT Kunder.Kundnr, Kunder.Fnamn, Kunder.Enamn, Lager.Artnr
FROM Kunder
INNER JOIN Lager
ON Lager.Artnr=Order.Artnr AND Kunder.Kundnr=Order.Kundnr
WHERE Kunder.Kundnr= [Ange Kundnr];

I appreciate all the help I get.

Deeo

View 12 Replies View Related

Run This Code In Sp

Dec 7, 2007

Is it possible to run this code in a stored procedure ? if so how ? TIA

Dim sw As StreamWriter = New StreamWriter("Transactions.txt")
Dim line As String

Try
cn.Open()
cnCmd.CommandText = "Select description, document, part_num, cur_qty, cond_code, generic1, generic2 from tblinventory"
cnCmd.Connection = cn
drDB = cnCmd.ExecuteReader

Dim description As String, document As String
Dim part_num As String, cur_qty As String
Dim cond_code As String, generic1 As String
Dim generic2 As String

While drDB.Read
description = drDB.Item("description")
document = drDB.Item("document")
part_num = drDB.Item("part_num")
cur_qty = drDB.Item("cur_qty")
cond_code = drDB.Item("cond_code")
generic1 = drDB.Item("generic1")
generic2 = drDB.Item("generic2")

line = description.PadRight(30) & document.PadRight(25) & "LINE," _
& part_num.PadLeft(10, "0") & ",RIC,UI," & cur_qty.padleft(5, "0") _
& "," & cond_code.PadRight(3) & "," & document.PadRight(25) _
& "," & generic1.PadRight(20) & "," & generic2.PadRight(20)

sw.WriteLine(line) ' change padding parameter accordingly!
End While
drDB.Close()

cn.Close()

View 9 Replies View Related

RS ASP.Net Code

Jul 23, 2005

Does anyone know if the source code for the ASP.Net application thatcomes with Reporting Services is available for viewing somewhere?

View 1 Replies View Related

C# Code In Rs?

Jul 1, 2007

Hello.
I wrote somw code in VB in the code section of a report.
I saw in a few places thet u can wrtie in C#.

Is it true? When I tried I got an error.

Thanks

View 3 Replies View Related







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