This is what I want to do. For a data acquisition query, if condition
A is met, set weight to 1, else if condition B is met, set weight to 2
etc.
Is this possible? I thought about using CASE function but to no
avail.
Using the classical Northwind db as target db, and its employees table
as target table, if [note] column contains 'BA', then I want to set
weight to 1, else if it contains 'BTS', then I want to set weight to 2
etc. Weight or the like is an auxillary artificially created column.
Something like
DECLARE @col int
select firstname, lastname, @col =
(
CASE notes
when '%BA%' then 1
when '%BTS%' then 2
else 0
END as aiCOL
)
-- and I'd like to alias @col as aiCOL or whatever
from employees
where notes LIKE
'%BA%'
OR
notes LIKE '%BTS%'
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset  (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am usingÂ
How much performance hit should I expect to use FOR XML to generate XML string directly from SQL Server 2000? I like this approach because it is simple and easy. However, others told me that it has a big performance hit. They prefer to use a COM+ component to create XML string from a Resultset. Any suggestions? Thanks.
Hi,I have 3 tables as follow :Kanji :kanji_id....References :ref_id....KanjiRefskref_idkanjikref_idrefkref_value....So, there is a many-to-many relationship between Kanjis and References(one kanji may have more than one reference type, and a reference typemay be set to more than one kanji).For example, one kanji may have the value 'abc' for reference type #1,and the value 'def' for reference type #2, another kanji may also havethe reference type #1, but have instead the value '123' and so on...I have two questions :1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)within their list of references?2) How to get the value of all the kanjis that have the reference #3,but still get other kanjis that do not have the reference #3...forexample, if I had 3 kanjis, with the two first having values 'abc' and'def' for reference #3, and the last one having no reference #3, I'dlike to get that resultset :kanji_id kref_value1 'abc'2 'def'3 NULLI manage to get all the kanjis that have reference # 3 with thefollowing query :SELECT kanji_id, kref_valueFROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_idWHERE kref_idref = 3however, this obviously does not include kanjis having no reference#3...any help would be greatly appreciated, thanks! :)ibiza
I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?
Hi I have 2 Sql statements within a stored procedure.But second one crashes my app - I'm getting object not found I need some help when writing several querys into the same sp, do I have to define some thing special? thanks
I am trying to write a select statement that will return the values of two columns from two different tables in the same statement. I believe the join word needs to be used somewhere, but I am having difficulty with it.
Table1 (table name) Column1 (column name) ------ a b
Table2 (table name) Column2 (column name) ------ c d
According to the BOL documenation of the ExecuteSQLTask you can populate a variable with a resultset. I specified a variable of type 'Object' and followed the instructions and everything is fine.
Now I have TWO issues.
1) I want to filter some unwanted rows from this variable and add into another recodset. For filtering i am using foreach loop and putting condition on variable. It works fine till here, but how to update another 'Object' type variable.
2) How to make the result set as source in a data flow task. I want the filtered result set to become source in a data flow
I am using the SQL Server 2005 JDBC driver. I need to write a piece of code that makes use of the streaming ResultSet. That is as soon as I get the first row, a worker thread should be able to begin processing on it without waiting for the second row to arrive. When the second row arrives, the second worker thread should start processing on this new row without waiting for the third row to arrive and so on. Usually, with a ResultSet, I need to wait for all the rows to arrive first before I can start navigating the rows in the ResultSet. But in my code, I need to start navigating the ResultSet even as more rows are pouring in from the DBServer. How can I do that? Any pointers in this direction will be helpful. Further, I want to know will setfetchsize be of any help here? If I set the setfetchsize value to 1, does that mean that as soon as I get the first row, I can start working on this row of the resultset(viz start navigating the ResultSet) without waiting for the second row to arrive in the ResultSet?
Hi, I have used Red Gates SQL bundle to make an identical db on sql2005 express from sql 2000. When i run my query against the sql2000 i get what I want, but when i run it against the sql2005, i get nothing. Anyone got any suggestions to why? web.config: <!-- <add name="TPLConnectionString" connectionString="Data Source=[SQL2000],1433;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=??????;Password=????????;" providerName="System.Data.SqlClient" /> --> <add name="TPLConnectionString" connectionString="Data Source=[SQL2005]\SQLEXPRESS,1433;Integrated Security=true;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=?????;Password=???????;" providerName="System.Data.SqlClient" /> default.aspx.cs dbConnection.Open(); String ISPQuery = "SELECT STATEMENT"; SqlCommand command = new SqlCommand(ISPQuery, dbConnection); SqlDataReader reader = command.ExecuteReader();
I have a sp: mysp_getstuff it contains the following:SELECT Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?
I am having some problems exporting to Excel using SQL 7.
I have a DTS package which runs a query that returns one value and pumps the data to an Excel spreadsheet. Everything is working except the data is not populating the correct cell in the spreadsheet. How can I control which cell SQL pumps the data to?
I'm trying to number rows in a resultset by a grouping.
e.g. if my data looks like this:
personname persondata ----------- ----------------- person1 data person1 more data person1 other data person2 stuff person2 more stuff person2 even more stuff person2 lots of stuff person2 last bit of stuff person3 info person3 more info
I want it to return this, with x as a sub-numbered value (like an identity for each grouping):
x personname persondata -- ----------- ----------------- 1 person1 data 2 person1 more data 3 person1 other data 1 person2 stuff 2 person2 more stuff 3 person2 even more stuff 4 person2 lots of stuff 5 person2 last bit of stuff 1 person3 info 2 person3 more info
I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table.
But duplicate records are being returned in the resultset. How do I eliminate duplicate records?
This is my query.
SELECT table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id
I tried doing DISTINCT here but with no success.
SQL with distinct clause.
SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id
This is the error I get: The text data type cannot be selected as DISTINCT because it is not comparable
I have the below CTE that I just can't seem to get to give me the right results. Basically what im trying to do is use the first query to show the "sources" that are involved in each inquiry and the second query to show which of those have became "admissions" the thing is the counts of the sources when the CTESource query is ran alone is different than my query to join the two tables.
Code: With CTESource(Total, ID, Source, Program) AS ( SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
[code]....
The total inquiries can be higher than the source totals since a source isnt required in the system as well as there does not have to be admissions regardless of inquiry count.
The output I need is ( '|' means cols separator) : Manufact1 | No of recs from HW for this Manufact | nulls ... null | null | HW1 from Manufact1 details ... null | null | HW2 from Manufact1 details ... Manufact2 | No of recs from HW for this Manufact | nulls ... null | null | HW3 from Manufact2 details ... ...
Hello all, I want to be able to be able to return a resultset from a Stored Procedure.
Something like :
CREATE PROCEDURE LSNOnAJob @MyJobNo AS INT,@MyLsn VarChar(10) OUTPUT
AS
SELECT @MyLsn = dbo.TSample.ISmpShortCode FROM dbo.TJob INNER JOIN dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN WHERE (dbo.TJob.IJobN = @MyJobNo) GO
I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.
I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.
I have made some stored procedures to check if a user is involved with a certain record. basically every stored procedure contains the following logic.
example spCheckClientRelated: select @res = count(*) from client_role where client_id = @cid and employee_id = @eid
if (@res = 0) begin ... next select end if (@res = 0) begin ... next select end .... return @res end
so far so good. But the final check in CheckClientRelated tests if a user is related to one of the sales projects for that client.
I allready have the spCheckSalesProjectRelated that returns 1 or 0 similar to the example above
so I want to find an efficient method that selects all the sales_project_id 's from the sales_project table where client_id = @cid (i use offcourse select @sid = sales_project_id from sales_project where client_id = @cid at the moment)
And then I have to execute the spCheckSalesProjectRelated method for each @sid and @eid. This if offcourse where my problem is located. I don't know how to do a fast check for every selected @sid, until spCheckSalesProjectRelated returns 1
As you probably can determine from my question, sql is not really my domain, and I'm certainly not an expert, but I don't mind reading or looking up some stuff, so even a clue or a direction to look in would be most appreciated
Hi,I would like to write a stored procedure that returns a resultset.e.g. select student_id, student_subject, subject_marksfrom student_resultswhere student_class_no = ?The input parameter is student_class_no (see ? as per above SQL).The output is a resultset, with each result consisting of student_id,student_subject, subject_marks.Can anyone advise how this is done in sql server stored procedure?Thanks,June Moore.
Hello,Quick, and possibly strange, question.I am doing some work testing the running time of some dynamic SQLstatements on a remote machine. What I would like to do is executethe SQL on the remote machine, without returning the result set to thecalling machine (this would skew the results, as my connection to theremote machine is rather slow).I believe SET ROWCOUNT 0 would work in principle, but thedocumentation says:"Causes Microsoft® SQL Server™ to stop processing the query after thespecified number of rows are returned."I don't want processing to stop until all the records have beenidentified. I also don't know if "SET ROWCOUNT" works with openquery(althought a non-openquery solution would also be acceptable).The code will be running in a stored procedure, if that matters.Any input would be much appreciated.Phil
Code Snippet CREATE PROCEDURE proc @param1 INT @param2 INT AS BEGIN
-- how to affect a variable to this resultset in order to iterate and get fields ? SELECT field FROM table END GO
In this proc, I would like to iterate through the resultset without using cursors, but I have no idea of the way to achieve it (I'm a very beginner in T/SQL)
And, in the case I would like to read this resultset through C# code, how can I return this resulset in my C# code in order to read it ?
I am using Visual Studio 2005 and SQL Server mobile edition. From Visual Studio I went to Data->Add New Data Source. From there I created and added an SQL server Mobile database. I want to use result sets instead of data sets. A file was added to my profect called: "databaseNameDataSet.xsd". In the properties for that file, when I change the Custom Tool property to MSResultSetGenerator, I get the following error:
Failed to generate ResultSet code. Typed ResultSets are only valid for Microsoft SQL Server Mobile Edition databases.
But I am using a SQL Server Mobile Edition database. Any help will be appreciated.
I am new to SQL2005 SSIS. I would like to know whether the output of a data flow can be the data source of UPDATE SQL or not. If yes, how?
My situation is I use data flow task to select and transform some data from a Table A. Then, I transfer then output to a resultset destination and store the value in a variable X.
After data flow task finish, I want to use the data to perform update SQL by SQL task in control flow e.g.
update table B set fieldB1 = fieldA1 from X where B.key = X.key
I know I can store the output in data flow in a temp table, run SQL and then drop the temp table. But is it approcach is slow? Using variable/resultset is much faster?
I have constantly opened ResultSet. As i know, any changes made to table on which resultset was created immediatly affect the resultset (if it is sensitive), so the resultset data are always up-to-date. But how can i monitor the exact moment of resultset update in order to reread data immediatly & refresh my UI?
