Need Transact-SQL Code
Aug 21, 2006Is there any transact code for sql server that I can type out to view all of the relationship Definments of the current database or even an individual table.
View 4 Replies
ADVERTISEMENT
Transact Sql 2005 Code Changes
Aug 28, 2006Have there been any updates to sql 2005 transact codes, because I tried to use some transact code that I used in sql 8.0 and in sql 2005 on the same database and it did not work.
Here is the code I used
Select p.pub_name,
e.fname + ' ' + e.lname "Employee name",
e.job_lvl
from publishers P inner join employee e on p.pub_id = e.pub_id
where e.job_lvl > 200
Transact-sql Code Does Not Work. HELP!
Aug 1, 2006Hi all, I am new to transact-sql and hoped that someone here might be able to help. I have a db with a field called "part" ... part contains text in the format:
xxxxx-xx-xxxxx
(the number of x's before or after each hyphen vary) such that I could have xxx-xxxxx-xxx as the part.
Someone wrote this transact-sql to take my current "part" field and move it to three different part fields ... a,b,c
If xxxxx-xx-xxxxx was the part in the original db, then a would contain xxxxx .. b contains xx ... c contains xxxxx
Make sense? Here is the code. It doesn't work and I can't seem to figure out why! It looks like it is just trying to find the hyphens and cut out the text from that... but it fails with an error that says "invalid length parameter passed to the substring function". Note, if I take off the "-1" from each line below... then the function works but produces the wrong data.
char(45) = "-" (hyphen)
UPDATE [dbo].[parts] SET
a = SUBSTRING(part,1,CHARINDEX(CHAR(45),part)-1),
b = SUBSTRING(part,CHARINDEX(CHAR(45),part)+1,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))-1),
c = SUBSTRING(part,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))+CHARINDEX(CHAR(45),part)+1,LEN(part))
GO
Does anyone know what I can do to fix this or is it much more difficult to do than what it seems?
Thanks!
Transact-sql Code To Display Preformatted Tables
Mar 3, 2006altimebest1 writes "Please show sql codes to creating a table showing Value and quantity under a heading, the month the data was collected or the value and quantity in the 1st, 2nd, 3rd and 4th quarter values under a heading Year.
My source table is:
comCode char 7
monthCode char 2
year char 4
countryCode char 3
quantity int
value money
insurance money
freight money
i also have a lookup table for:
Commodities Countries
comCode char 7 countryCode char 3
comDescriptions varchar 255 countryName varchar 255
the format of the table i wish to create
Com Commodity Description Jan
Code Country of Destination ValueQuantity
Thank you for helping me and I been a constant visitor to your site and it's wonderful and gives great help to sql enthusiasts."
Transact SQL :: Inserting Exclusion Table Into Existing Code?
Sep 11, 2015below is some code I use to identify where a patient has attended the ED department whilst also admitted as an Inpatient. This report works fine. However while most of the results are recording issues to be corrected some of them are real and as so can be excluded from the report.
Is there a way I can build in an exclusion table which would include:
SELECT
IP_ADMISSION.HeyNo AS HEYNo
,IP_ADMISSION.NHSNo2 AS NHSNo
,IP_ADMISSION.Forename AS Forename
,IP_ADMISSION.Surname AS Surname
,IP_ADMISSION.SourceAdmNatCode AS SourceAdm
[code]....
Transact SQL :: Convert Numbers To ASCII Code In Server
Jul 29, 2015This is a string i am getting from sql server.
00000006637120150522187449637100 34
10-000000003444378351108502007
01016800002413
10-000000091541378538466562009
01016800002420
[Code] .....
Transact SQL :: How To Hard Code Mention Date Range In SP To Get Expected Results
Oct 7, 2015how to hard code mention date range in my SP to get expected results in my query 01/01/2012 to 12/31/2012
DECLARE @ACCOUNT AS INT
DECLARE @POSTING_DATE AS DATETIME
DECLARE @FIRST_POSTING_DATE AS DATETIME
SET @POSTING_DATE = {?POSTING_DATE}
SET @ACCOUNT = {?ACCOUNT}
[code]...
Transact SQL :: Get List Of Items Present In Order Based On Confidentiality Code Of Product
Sep 29, 2015I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.
I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.
whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.
If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.
Table 1:Order
Order_Id Order_No Customer_Id
2401 1234567 23
2402 1246001 24
2403 1246002 25
Table 2 : OrderedItems
OrderItem_Id Order_Id Item_Id Sequence
1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1
Table 3: ItemMaster
Item_Id Item_Name confidentCode
1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8
Out put for 1st grid
**Note :** Logged-in user have confidentiality code 6
Order No Customer
1234567 23
1246001 24
3rd order is not displayed in the grid
After user selects the 1st order in the grid then the items present in that 1st order should be displayed as
1001 Rice
the second item not displayed because that having confidentiality code greater than user.
After user selects the 2nd order in the grid then the items present in that order should displays
1005 Badham
1007 Oil
I need the query to display the order details in 1st grid.
Transact SQL :: Ensure Code Non Regression By Keeping Consistent Signature For Procedure / Views And Function
Jul 28, 2015In the 70-461 objectives it says: Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications...I think I understand what this means in general. They want us to be able to create a view that will still be able to call the original data even if the table is modified. In other words, the view table shouldn't easily be broken. ie, type a code that does NOT ensure non regression, then change the code so that it does ensure non regression.
View 4 Replies View RelatedTransact SQL :: Encrypt Java Code Using With Encryption Clause From Server Stored Procedure Or Function
Nov 3, 2015How to encrypt the java application code using the 'with encryption' clause from sql server stored procedure or function.
View 3 Replies View RelatedLooking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task
Apr 19, 2007I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005
Jul 27, 2006Hi 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!
How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)
Mar 28, 2007Dear 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
Many Lines Of Code In Stored Procedure && Code Behind
Feb 24, 2008Hello,
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
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.
Putting SqlDataSource Code In Code-behind
Jan 25, 2007Hi,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, 2006Hi:
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
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.
Jan 28, 2008Hi 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
Need T-sql Code
Mar 11, 2008Right 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
Need Help With Code
Sep 7, 2000Can 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 ('.
Code Help!!!!
Jun 15, 2000I 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
The Code Behind Sum().
Jun 7, 2006Hi, 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
Code Help!
Nov 17, 2004Is there a store procedure to determine if you have outlook on your server to send mail.
Lystra
Help With Code
Dec 2, 2004I 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
SP Code
Sep 26, 2005Hi,
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
T-SQL Code Help.
Mar 4, 2004I'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.
SQL Code Help!!
Jan 21, 2006Server: 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
How To Code This ?
May 14, 2007Hi,
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.
How To Code?
May 14, 2007Hi,
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.
Help With SQL-code
Oct 25, 2007Hello 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
Run This Code In Sp
Dec 7, 2007Is 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()
RS ASP.Net Code
Jul 23, 2005Does anyone know if the source code for the ASP.Net application thatcomes with Reporting Services is available for viewing somewhere?
View 1 Replies View RelatedC# Code In Rs?
Jul 1, 2007Hello.
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