I’m thinking about the best way to run these queries, which need to be run regularly.
The first query is two tables linked, one is a data table containing a unique person identifier and three activity fields, the second a lookup table with an activity_type linked to the activity in the table data.
Data Table
PersonID
Lots of other fields
Activity1
Activity2
Activity3
The ACTIVITY fields can contain data anywhere between all being NULL and all being complete.
Lookup Table
ActivityID
ActivityDesc ( which appears in Activity 1 – 3)
ActivityType
I’d like to create a function which will create a recordset containing the Person ID and the Activity Type. I am unsure as to whether to do this in a way which will create one record for each person, or potentially 3 records for each person. This is how I have done the 3 records:
SELECT PersonID, Activity1 As Sport, ActivityType
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity1 = dbo.tblLUActivityType.ActivityDesc
UNION
SELECT PersonID, Activity2 As Sport, ActivityType
[Code] ...
And this is how I have done the 1 record:
SELECT ClientID,
Activity1,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity1) As ActivityType1,
Activity2,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity2) As ActivityType2,
Activity3,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity3) As ActivityType3
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity3 = dbo.tblLUActivityType.ActivityDesc
Order by PersonID
The reason I would like to do this is because I need to create a stored procedure which returns one record per person with two fields (Person Id, ActivityType) which states their ActivityType depending on certain rules:
Rule 1: If any of Activity 1 – 3 are ‘O’ report the Person ID and ActivityType = ‘O’
Rule 2: Of the rest of the recordset, if any of Activity 1 – 3 are ‘N’ but none of Activity 1-3 are ‘O’ or ‘A’ then report the Person ID and ‘N’.
Rule 3: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ but none of Activity 1-3 are ‘O’ or ‘N’ then report the Person ID and ‘A’.
Rule 4: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ and one of the is ‘N’ then report the Person ID and ‘AN’.
At the end this I’m looking for one recordset with two fields containing a personID and one of ‘O’, ‘A’, ‘N’ or ‘AN’. I can do the first part of this in any way necessary, as long as the second part returns the recordset I need.
Hi guys, I know this might be the wrong place to post this, but I thought I would try since all of you are very smart here :). I have a huge problem with an app that I inherited. It is a VB6 app that used RDO. I am trying to convert it to use ADO, but the problem I am running into is we have allot of stored procedures with OUTPUT parameter's, so instead of the procedure having a SELECT to pass back the value, it is passed back via the OUTPUT parameter. With RDO this is done easily with just passing back the parameter to a variable. I am not sure how to do this with ADO. Please shine some light for me.
Here is an example: RDO code: Private rqAddRecord As RDO.rdoQuery Private rs As RDO.rdoResultset Set rqAddRecord = SQLServerDB.CreateQuery("", MakeSP("sp_UpdCourier", 11))
IF @ActionCode IN ('I','U') AND @CourierDepartment = '' BEGIN RAISERROR (50075, 16, 1) RETURN END
-- Insert IF @ActionCode = 'I' BEGIN -- Validate Existance IF EXISTS ( SELECT* FROMtblCourier WHEREDeliveryDate= @DeliveryDate ANDCourierServiceID= @CourierServiceID ANDCourierID= @CourierID ANDCourierDepartment= @CourierDepartment ANDTimeStart= @TimeStart ANDTimeComplete= @TimeComplete ) BEGIN RAISERROR (50002, 16, 1) RETURN END
/* ** Verify if entering another shift that that Times do not overlap */ IF EXISTS ( SELECT* FROMtblCourier WHEREDeliveryDate= @DeliveryDate ANDCourierServiceID= @CourierServiceID ANDCourierID= @CourierID ANDCourierDepartment= @CourierDepartment ANDNOT ( (@TimeStart< TimeStart AND @TimeComplete < TimeStart) OR(@TimeStart> TimeComplete AND @TimeComplete > TimeComplete) ) ) BEGIN RAISERROR (50044, 16, 1) RETURN END
IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RAISERROR ('Failed inserting Courier values', 16, 1) RETURN END
COMMIT TRANSACTION
--SELECT @CourierDeliveryID
RETURN END
-- Update IF @ActionCode = 'U' BEGIN /* ** Verify if entering another shift that that Times do not overlap */ IF EXISTS ( SELECT* FROMtblCourier WHEREDeliveryDate= @DeliveryDate ANDCourierServiceID= @CourierServiceID ANDCourierID= @CourierID ANDCourierDepartment= @CourierDepartment ANDNOT ( (@TimeStart< TimeStart AND @TimeComplete < TimeStart) OR(@TimeStart> TimeComplete AND @TimeComplete > TimeComplete) ) ANDCourierDeliveryID!= @CourierDeliveryID ) BEGIN RAISERROR (50044, 16, 1) RETURN END
IF EXISTS ( SELECT* FROMtblCourierDetail d WHERECourierDeliveryID= @CourierDeliveryID ANDCONVERT(CHAR,DeliveryTime,14) NOT BETWEEN CONVERT(CHAR,@TimeStart,14) AND CONVERT(CHAR,@TimeComplete,14) ) BEGIN RAISERROR (50059, 16, 1) RETURN END
IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RAISERROR ('Failed updating Courier values', 16, 1) RETURN END
-- If date changed, then update the Delivery Times IF CONVERT(CHAR, @DeliveryDate, 101) <> CONVERT(CHAR, @ExistingDeliveryDate, 101) BEGIN SELECT@NbrDays = DATEDIFF(dd, @ExistingDeliveryDate, @DeliveryDate)
This stored procedure accepts a ProductID as an input parameter and returns product information to the caller in the form of output parameters. A one line recordset is not used in order to gain a performance increase. It is used primarily on the product details screen.
Can anyone shed some light on if this is a best practice when retrieving a single row from the db? Is the performance gain very noticable? I mean it makes sense and all, I just see a lot of examples using a datareader when there is more than 1 column, and ExecuteScalar() when there is one.
I just wrote an application that might benefit from this if its worth the trouble of changing a couple methods in my data layer calls to ouput parameters.
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:
Declare @tbl Table(id int, name varchar(100), age int) Insert Into @tbl(id, name, age) Values(1, 'James, Lee', 28), (2, 'Mike, Richard', 32), (3, 'Leon Wong', 29) Select * From @tbl Order By age
It works well in "SQL Query Ananizer". But return no records in ASP page.
I am using SELECT FOR XML which is working great.My problem is writing the results out to the filesystem.I am using spWriteStringToFile procedure that uses Scripting.FileSystemObject to write the file.The file gets written and all of the "xml" is there, but there are no CR/LFs and parsers, browsers and validators don't like it.What can I do to get a more usable output file?
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s SET Exception_Ind = 1 OUTPUT s.Master_Id, s.TCK_NR INTO #temp2 FROM Master_Summary s INNER JOIN Exception d ON d.Id = LEFT(s.Id, 8) AND d.Barcode_Num = s.TCK_NR WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113 The multi-part identifier "s.Master_Id" could not be bound. Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
I have 4 different queries in one SSMS New query window that are returning expected results in 4 resultsets. However I want to output these results to a single .js file one after the other in the order of queries. Is that possible?
I am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?
USE [MyDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.
We are using Confluence 5.6.5; essentially I want to output some data from a SQL Server 2012 Database onto a Confluence page; how to go about achieving this?
I used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.
What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.
Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.
In Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code?
create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,) AS BEGIN BEGIN TRY BEGIN TRANSACTION TR1 ..... COMMIT TRANSACTIOn TR1 END TRY
How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE TABLE [dbo].[MRMReadings]( [SiteIncomeID] [int] IDENTITY(1,1) NOT NULL, [SiteID] [nchar](5) NOT NULL,
[Code] ....
Is it possible to return the data in the following format merging 2 lines of data into one output:
Suppose we have the following table in our database;
CREATE TABLE [dbo].[PERMISSION]( [ID] [int] IDENTITY(1,1) NOT NULL, [USERID] [int] NOT NULL, [STARTTIME] [smalldatetime] NOT NULL, [ENDTIME] [smalldatetime] NOT NULL, [REASON] [nvarchar](250) NULL, [PERMISSIONTYPEID] [int] NOT NULL,
[code]....
This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;
well, not only this statement gives an error while executing; but also, no such usage defined in the
documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this; command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime); insert PERMISSION output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar
code]....
No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.
I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to automate this script means wants to create a job which runs on a Friday basis and save the output results of the query as a excel file and then automatically sends the mail to everyone.
I want to run a powershell script using xp_cmdshell, put the results into a temp table and do some additional stuff.I'm using:
CREATE TABLE #DrvLetter ( iid int identity(1,1) primary key ,Laufwerk char(500), ) INSERT INTO #DrvLetter EXEC xp_cmdshell 'powershell.exe -noprofile -command "gwmi -Class win32_volume | ft capacity, freespace, caption -a"' select * from #DrvLetter SQL server is cutting off the output, what I get is (consider the 3 dots at the end of a line):
I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string. For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.
Presently the syntax I am using is PRINT STR(@CLUSTER,3) . But if @CLUSTER which is numeric is less than three characters I get spaces in front.
Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn FROM ( SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
I'm trying to decide whether MS SQL will allow me to accomplish the following objectives at no cost, or whether I'd eventually have to pay for an MS SQL upgrade to accompish my objectives.
I have big, unrealistic dreams. I want to create a humorous newsblog into which I would post more than a dozen times a day. Most of the posts would have large photographs. Presumably, I'd archive the posts by subject and ranking and 'most viewed," etc., using a database.
1) Will the space limitations of the MS SQL Express edition be an issue after a while?
2) Could I hire a web developer to help me from a remote location, once the website is large enough to warrant expansion? Or does the MS SQL Express edition allow only one user? I read something I didn't understand about CPU restrictions.
3) I'm confused because web hosts advertise the availability of MS SQL databases on their server...so does that mean I wouldn't have to buy an upgrade if it became neccessary? (I know, I'm shockingly uneducated.)
4) I'm going to buy Office 2007. Is it important to purchase a package that includes Microsoft Access, given my goals?
5) Any other thoughts in plain english on how the MS SQL express edition imposes limitations....basically, I don't understand how MS SQL Express might limit me down the road if the site were actually a success. What would have to happen before I would be forced to spend a lot of money on an upgrade later?
I'm almost completely new to computing. I've read a bunch of criticisms of MS SQL Express on internet forums that I didn't understand, but that really made me worried about my decision to go with Microsoft Products and Asp.net web hosts. (I understand some people have an irrational dislike of Microsoft, but there was A LOT of bashing.)
please do help. Have been wrestling with this for about 3 hours and read a buncha forums + searched the tutorial lists.. AARRGH!
Anyhow,
I have to paginate a datalist (and I really can't use a datagrid because of the layout, blame the bluddy graphic designer)
I want to return the top 8 rows, the next 8 top rows and so on.
This is the sql string I have: 'retrieve pagination in order to construct the rest of the sql string Dim startrec As Integer If pageno = 1 Then startrec = 0 Else startrec = (pageno - 1) * pagesize End If ' this builds the sql string, getting ONLY the records we need for this. Page size is a constant expressed on the base page ' startrec is the record where I want to start in the db. strsql = "select top " & pagesize & " * " & strsqlbasic & " and itemID>" & startrec & " order by itemnotes1 asc" noresults.text = strsql & " <br> " & searchwhat & searchfor
strsqlbasic is constructed elsewhere and is just the 'from X where y = var
Of course, this returns all records where the value of itemID is greater than the value of startrec. How would I change this so it returns the next 4 rows starting from the row number given by the value of startrec?
Orcale uses a psuedo colum ROWNUM to limit the number of rows returned by a query. Does MSSQL server 6.5 provide some similar feature (or any indirect way of doing that).
I have a problem on microsoft sql server 2000. I need to limit the amount of resources(eg cpu, mem..) a query uses Is there a way to do this in SQL server 2000
I'm trying to write a query where if the value of one of the paramters is equal to "-1" it will not include one of the search criteria. For example I have a param called "Gender". 0 = Male and 1 = Female. If the value of the "Gender" param is "-1" I'd like it to find either Males or Females.
Code (please not this doesn't compile):
SELECT [UserID],[Gender] FROM [table_user] WHERE Country = @Country AND IsOnline = @IsOnline IF (@Gender != -1) AND Gender = @Gender
Obviously this doesn't work, but I think it conveys the gist of what I'm trying to do. Does the secret lie in default parameters? Or is my IF syntax just flat out wrong?
I am trying to convert some mySql code to sqlserver and was amazed to see that sqlserver has no functionality to specify the rows of a select statement. In mySql its e.g. "SELECT * FROM table LIMIT 20, 10" this returns ten rows starting from row 21. Using TOP in sqlserver only returns the top n rows but what if I want row 20 to 30? Is there an easy way that I have just missed or does one realy have to do something like "SELECT TOP 60 * FROM myTable WHERE id NOT IN (SELECT TOP 40 id FROM myTable);"?
Hello everyone.. I'm working here on a little problem which's driving me nuts : I have two tables :EQUIPMENT :
-EQID -EQName -EQblabla QUALITYCHECK :
-QCID -EQID <---- Is connected with EQUIPMENT -QCDATE -QCACTION -QCnextdate They're needed to manage Qualityactions on some machines we own. Every year, we do quality actions on every machine, after then, we Inserta new QUALITYCHECK-entry into our database and automatically theQCnextdate is generated with QCdate+365days. So far, so easy. So now, how will i do a query against the DB with the NEWEST QUALITYCHECK-entrys for every machine ? The result has to besmth. like that : EQID | EQName | QCDATE | QCNextdate
I am trying to limit the number of records to start in a position and show me "n" records. Here is an example query:
SELECT TOP 60 * FROM Entries WHERE Id NOT IN ( SELECT TOP 45 Id FROM Entries ORDER BY Id DESC ) AND Static = False AND Authorized = False ORDER BY Id DESC
The application retrieves limited records for paginated results. This way we can access large databases, but only return rows for one screen.
For example: Select row 1 - 10, then 11 - 20 then 21 to 30 etc.
With MySQL we can use LIMIT and OFFSET and it works great. MySQL SELECT <columns> FROM <tables> [WHERE <condition>] [ORDER BY <columns>] [LIMIT [offset,]howmany>]
Does anybody know how we can do something similar with MS SQL?
